Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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/

share|improve this answer
    
Also, as an aside, you might be happier with a monitoring system intended just for SQL in the long run, I.E. Red Gate's SQL Monitor. –  Kyle Brandt Aug 16 '11 at 17:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.