The gold rule of remote dba services providers is SQL Server must be monitored. When counters exceed or fall below critical values an alert must be sent to the support level DBAs. Any DBA can create an analogue of third party monitoring system such as Spotlight, HP monitoring system or CA monitoring system using SQL Server Agent alerts plus standard windows perfmon utility. The obvious advantage of this sql server monitoring solution is its price - it is absolutely free and quite reliable but the disadvantage is the lack of functionality. For instance you can't see the current state of sql server without having to log into this server. This sql server monitoring system will fit low budget projects and small business companies.

Monitoring system requirements

First we need to define what needs to be monitored on a sql server machine.

OS counters

CPU total load - 90%. Since there is no total cpu load counter in perfmon we can use Process Idle Processor time. When it goes below 10% it means total CPU load rises above 90%.

Memory Utilization

Virtual

Physical

Paging

 Logical volumes

Size

Fragmentation

Thoughput

Services

Antivirus

MSSQLServer, Sql Server Agent, MSDTC, MSSearch

wuauserv automatic updates

 Network interface

Input packets

Input errors

Output packets

Output errors

SQL Server counters

 System resources

Disk IO

Network IO

CPU load

Alerts

Server resources

Data Cache  Hit Ratio

Procedure Cache Hit Ratio

 Connections

 Locks

Total Deadlocks

Total Average Wait Time 

Total Locks

Total Blocking

Total Exclusive locks

File systems

Database counters

 All database errors with serverity more than 16

SQL Server monitoring configuration

First you need to configure database mail. After this is done 

Configure SQL Server agent operator

Right click on Operators and choose New Operator

In the popup dialog specify operator name and e-mail name for the operator

Alternatively run the script 

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'sqlmonitoring',
        @enabled=1,
        @pager_days=0,
        @email_address=N' This e-mail address is being protected from spambots. You need JavaScript enabled to view it '
GO

 Just replace This e-mail address is being protected from spambots. You need JavaScript enabled to view it with your real e-mail.

SQL Server related counters

Enable mail profile in Alert System of SQL Server agent

Right click on SQL Server Agent in SSMS and choose properties. Then go to Alert System nodeand mark Enable mail profile checkbox. Choose mail system and mail profile you configured before.

Or use the script

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'databasemail'
GO

where databasemail is the name of database mail profile.

You must restart SQL Server agent after mail profile has been enabled.

BAT file to send alert e-mails via sqlcmd using sp_send_dbmail

set mailbody=%1
set mailbody=%mailbody:~1,-1%
sqlcmd -S.\sql2008 -E -Q "declare @subject sysname; set @subject = 'Performance alert on ' + @@SERVERNAME; EXEC msdb.dbo.sp_send_dbmail @recipients=' This e-mail address is being protected from spambots. You need JavaScript enabled to view it ',@subject = @subject , @body = '%mailbody%', @body_format = 'TEXT' ;"

Save it under the path C:\sqlmonitoring\sendmail.bat .

Configure perfmon alert to send mails

The disadvantage of perfmon alert is their inability to send e-mails. The actions of alert can only be 

Log an entry in the application event log

Send a network message

Start performance data log

Run this program

As a remote dba service provider DB-Staff needs to receive e-maill regarding perfmon alerts so we use the bat file that executes sp_send_dbmail.

 To create new perfmon alert that sends emails run perfmon from start -> run. Go to alert node and choose New Alert Serttings...

Give a comment to these alert settings add needed counters and define the limits you need to be alerted when the values go over or under. Also define the interval of checks. This may be 1min - 6 hours depending on the importance of performance.

Go to the Action tab and mark Run this program checkbox. Find the bat file you created before

Press command line arguments buttons and choose what you want to see in the resulting e-mail

Click Ok.