Monitoring SQL Server 2008

Question

I understand SQL Server doesn't release the memory unless the OS needs it. Then, monitoring Available Bytes (free memory) is not the best way to monitor the service. What other variables can give me a real measure about the behaviour of the SQL Server? Maybe Pages/sec or Page Faults/sec? I'm using nagios to monitor the service and sometimes the alerts arise because a big query is executed.

Answer 1

You can get proper memory usage with SQL using the Perfmon counters, in particular the Total Server Memory. With Nagios you could use NSClient++ and nrpe to poll these counters. Here are some SQL Counters for your reference (With silly warning and critical levels):

define service {
        service_description     SQL Buffer Cache Hit Ratio
        use                     generic-service
        hostgroup_name          database_servers
        check_command           check_nrpe_counter!BufferCacheHitRatio!\\SQLServer:Buffer Manager\\Buffer cache hit ratio!100!200
}

define service {
        service_description     SQL Full Scans Per Sec
        use                     generic-service
        hostgroup_name          database_servers
        check_command           check_nrpe_counter!scans!\\SQLServer:Access Methods\\Full Scans/sec!100!200
}

define service {
        service_description     SQL Latch Wait Time in MS
        use                     generic-service
        hostgroup_name          database_servers
        check_command           check_nrpe_counter!ms!\\SQLServer:Latches\\Total Latch Wait Time (ms)!100!200
}

define service {
        service_description     SQL Batch Req Per Sec
        use                     generic-service
        hostgroup_name          database_servers
        check_command           check_nrpe_counter!req!\\SQLServer:SQL Statistics\\Batch Requests/sec!100!200
}

define service {
        service_description     SQL Re-Compilations Per Sec
        use                     generic-service
        hostgroup_name          database_servers
        check_command           check_nrpe_counter!req!\\SQLServer:SQL Statistics\\SQL Re-Compilations/sec!100!200
}

define service {
        service_description     SQL Total Memory in KB
        use                     generic-service
        hostgroup_name          database_servers
        check_command           check_nrpe_counter!mem!\\SQLServer:Memory Manager\\Total Server Memory (KB)!100!200
}

define service {
        service_description     SQL Memory Grants Pending
        use                     generic-service
        hostgroup_name          database_servers
        check_command           check_nrpe_counter!PenMemGrant!\\SQLServer:Memory Manager\\Memory Grants Pending!100!200
}

#define service {
#        service_description     SQL Mirror Log Remaining For Undo
#        use                     generic-service
#        check_command           check_nrpe_counter!kb!\\SQLServer:Database Mirroring(StackOverflow)\\Log Remaining for Undo KB!100!200
#}

#define service {
#        service_description     SQL Mirror Log Send Queue
#        use                     generic-service
#        check_command           check_nrpe_counter!kb!\\SQLServer:Database Mirroring(StackOverflow)\\Log Remaining for Undo KB!100!200
#}

#define service {
#        service_description     SQL Mirror Redo Queue
#        use                     generic-service
#        check_command           check_nrpe_counter!kb!\\SQLServer:Database Mirroring(StackOverflow)\\Log Remaining for Undo KB!100!200
#}

define service {
        service_description     SQL Number of Connected Users
        use                     generic-service
        hostgroup_name          database_servers
        check_command           check_nrpe_counter!UserCon!\\SQLServer:General Statistics\\User Connections!100!200
}

Example Command Def:

define command {
  command_name check_nrpe_counter
  command_line $USER1$/check_nrpe -H $HOSTADDRESS$ -c CheckCounter -a "Counter:$ARG1$=$ARG2$" ShowAll MaxWarn=$ARG3$ MaxCrit=$ARG4$
}

Lastly, you might be interested in Brent Ozar's recommended SQL Perfmon counters: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/