sql-server – 监控SQL Server 2008

发布时间：2020-12-12 06:20:07 所属栏目：MsSql教程来源：网络整理

导读：我知道除非操作系统需要,否则SQL Server不会释放内存.然后,监视可用字节(可用内存)不是监视服务的最佳方法.还有哪些其他变量可以让我真正衡量SQL Server的行为？可能是Pages / sec或Page Faults / sec？我正在使用nagios来监控服务,有时会出现警报,因为执行

我知道除非操作系统需要,否则SQL Server不会释放内存.然后,监视可用字节(可用内存)不是监视服务的最佳方法.还有哪些其他变量可以让我真正衡量SQL Server的行为？可能是Pages / sec或Page Faults / sec？
我正在使用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 ManagerBuffer 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 MethodsFull 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:LatchesTotal 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 StatisticsBatch 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 StatisticsSQL 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 ManagerTotal 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 ManagerMemory 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 StatisticsUser 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/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!