我知道除非操作系统需要,否则sql Server不会释放内存.然后,监视可用字节(可用内存)不是监视服务的最佳方法.还有哪些其他变量可以让我真正衡量sql Server的行为?可能是Pages / sec或Page Faults / sec?
我正在使用nagios来监控服务,有时会出现警报,因为执行了大查询.
我正在使用nagios来监控服务,有时会出现警报,因为执行了大查询.
解决方法
您可以使用Perfmon计数器,特别是总服务器内存,使用sql获得正确的内存使用量.使用Nagios,您可以使用
NSClient++和nrpe来轮询这些计数器.以下是一些sql计数器供您参考(具有愚蠢的警告和关键级别):
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 }
示例命令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$ }
最后,您可能对Brent Ozar推荐的sql Perfmon计数器感兴趣:http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/