本文转载自微信公众号《DBA闲思杂想》，作者潇湘隐士。转载本文请联系DBA杂念公众号。如何采集SQLServer数据库服务器CPU的一些指标？我们知道，一些监控工具（如Zabbix）可以方便快捷的获取CPU利用率等指标，但是Zabbix需要在服务器上安装客户端，那么是否可以通过SQL账号，??直接获取CPU利用率等指标，SQLServer数据库实例CPU利用率（InstanceCPUUtilization）、CPUSignalWaits等指标？如下图所示：在SQLServer中，其实有一个没有文档的DMV视图sys。dm_os_ring_buffers，我们可以从这个DMV中得到服务器CPU使用率，数据库实例的CPU使用率，脚本如下/************************************************************************************************************************--脚本名称：get_cpu_utilization_his.sql--脚本作者：--创建日期：2017-05-28*************************************************************************************************************************脚本功能：查看SQLServer数据库实例服务器的CPU利用率信息********************************************************************************************************************注：1：默认情况下，只能从sys.dm_os_ring_buffers中获取最近256分钟的数据（间隔为1分钟）。这个DMV没有文档（undocumented），所以要获取整个服务器的cpu利用率，必须定时采集数据。2：cpu_ticks指定当前CPU时钟周期数。CPU时钟周期数是从处理器的RDTSC计数器获得的。这是一个只会增加的数字。不能为空。ms_ticks指定自计算机启动以来的毫秒数。不能为空。变量@ts_now可能会有毫秒级的差异。*******************************************************************************************************************************本脚本支持SQLServer2008、2012、2014、2016、2017****************************************************************************************************************************更新：2017-05-28创建此脚本2019-10-15添加OSCPUUtilization(%)************************************************************************************************************************/DECLARE@ts_nowBIGINT;SET@ts_now=(SELECTcpu_ticks/(cpu_ticks/ms_ticks)FROMsys.dm_os_sys_infoWITH(NOLOCK));SELECT@@SERVERNAMEAS[ServerName],DATEADD(ms,-1*(@ts_now-[timestamp]),GETDATE())AS[EventTime],SQLProcessUtilizationAS[SQLServerProcessCPUUtilization(%)],100-SystemIdle-SQLProcessUtilizationAS[OtherProcessCPUUtilization(%)],100-SystemIdleAS[OSCPUUtilization(%)],SystemIdleAS[SystemIdleProcess(%)]FROM(SELECTrecord.value('(./Record/@id)[1]','int')ASrecord_id,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')AS[SystemIdle],record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')AS[SQLProcessUtilization],[时间戳]FROM(SELECT[时间戳],CONVERT(XML,record)AS[record]FROMsys.dm_os_ring_buffersWITH(NOLOCK)WHEREring_buffer_type=N'RING_BUFFER_SCHEDULER_MONITOR'ANDrecordLIKEN'%%')ASx)ASyORDERBYrecord_idDESCOPTION)ASx)ASyORDERBYrecord_一个没有文档的函数，只保存了256分钟的CPU使用率数据，每分钟一个数据，但是没有其他地方可以扩展保存更长时间的数据，所以如果想获取历史CPU使用率和其他指标数据，只能通过Python或jobs定期收集。下面是要在Python中使用的表和脚本。USEYourSQLDba;GOIFNOTEXISTS(SELECT1FROMsys.objectsWHEREtype='u'ANDname='SERVER_CPU_INFO')BEGINCREATETABLEdbo.SERVER_CPU_INFO([server_name]NVARCHAR(64),[event_time]DATETIME,[sqlserver_cpu_utilization]FLOATNOTNULL,[other_cpu_utilization]FLOATNOCLOTNULL[other_cpu_utilization],[ATNOCLOTNULL]FLOidle_cpu_utilization]FLOATNOTNULL,CONSTRAINTPK_SERVER_CPU_INFOPRIMARYKEY([server_name],[event_time]));ENDGOEXECsys.sp_addextendedproperty@name=N'MS_Description',@value=N'服务器CPU利用率信息表',@level0type=N'level0SCHEMA',=N'dbo',@level1type=N'TABLE',@level1name=N'SERVER_CPU_INFO';GOEXECsys.sp_addextendedproperty@name=N'MS_Description',@value=N'服务器名称',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'SERVER_CPU_INFO',@level2type=N'COLUMN',@level2name=N'server_name';GOEXECsys.sp_addextendedproperty@name=N'MS_Description',@value=N'事件发生日期时间',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'SERVER_CPU_INFO',@level2type=N'COLUMN',@level2name=N'event_time';GOEXECsys.sp_addextendedproperty@name=N'MS_Description',@value=N'SQLServer实例占用CPU百分比',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'SERVER_CPU_INFO',@level2type=N'COLUMN',@level2name=N'sqlserver_cpu_utilization';GOEXECsys.sp_addextendedproperty@name=N'MS_Description',@value=N'服务器CPU使用率',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'SERVER_CPU_INFO',@level2type=N'COLUMN',@level2name=N'os_cpu_utilization';GOEXECsys.sp_addextendedproperty@name=N'MS_Description',@value=N'空闲CPU利用率',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'SERVER_CPU_INFO',@level2type=N'COLUMN',@level2name=N'idle_cpu_utilization';GOEXECsys.sp_addextendedproperty@name=N'MS_Description',@value=N'其进程利用率',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'SERVER_CPU_INFO',@level2type=N'COLUMN',@level2name=N'other_cpu_utilization';GOMERGEINTOdbo.SERVER_CPU_INFOSUSING(SELECT@@SERVERNAMEAS[ServerName],DATEADD(ms,-1*((SELECTcpu_ticks/(cpu_ticks/ms_ticks)FROMsys.dm_os_sys_infoWITH(NOLOCK))-[timestamp]),GETDATE())AS[EventTime],SQLProcessUtilizationAS[SQLServerProcessCPUUtilization(%)],100-SystemIdleAS[OSCPUUtilization(%)],SystemIdleAS[SystemIdleProcess(%)],100-SystemIdle-SQLProcessUtilizationAS[OtherProcessCPUUtilization(%)]FROM(SELECTrecord.value('(./Record/@id)[1]','int')ASrecord_id,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')AS[SystemIdle],record.value('(./记录/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')AS[SQLProcessUtilization],[timestamp]FROM(SELECT[timestamp],CONVERT(XML,record)AS[record]FROMsys.dm_os_ring_buffersWITH(NOLOCK)WHEREring_buffer_type=N'RING_BUFFER_SCHEDULER_MONITOR'ANDrecordLIKEN'%%')ASx)ASy)T--ON(T.[ServerName]=S.server_nameANDT.[EventTime]=s.event_time)--注意：由计算[EventTime]存在偏差，可能导致出现重新记录，只它可以转换为分钟。ON(T.[ServerName]=S.server_nameANDCONVERT(VARCHAR(16),T.[EventTime],120)=CONVERT(VARCHAR(16),s.event_time,120))WHENNOTMATCHEDTHENSERT([server_name],[event_time],[sqlserver_cpu_utilization],[os_cpu_utilization],[idle_cpu_utilization],[other_cpu_utilization])VALUES(T.[ServerName],CONVERT(VARCHAR(19),T.[EventTime],120),T.[SQLServerProcessCPUUtilization(%)],T.[OSCPUUtilization(%)],T.[SystemIdleProcess(%)],T.[OtherProcessCPUUtilization(%)]);有时候项目经理会跟你说，我们数据库服务器的CPU压力不是很大，是不是CPU资源瓶颈了？你怎么判断？我们不能仅仅根据服务器的CPU利用率来判断。毕竟在业务高峰期，服务器的CPU利用率可能会比较高。使用“SignalWaitPercent”这个指标来判断是否存在CPU瓶颈，通过这个指标来判断CPU瓶颈是否影响数据库性能。在了解这个指标之前，我们首先要了解“CPU信号等待时间”这个指标。这个指标是什么？指的是进程或线程发出信号到开始运行的时间差。等待队列中的时间开销只是CPU等待。“SignalWaitPercent”指标描述了指令等待CPU资源的总时间的百分比。如果“SignalWaitPercent”很高，则可能表明CPU已被过度使用，迫使SQLServer进程进入任务等待状态。如果超过20%，说明CPU资源紧张，出现瓶颈。信号等待百分比信号等待百分比显示会话等待CPU可用的总时间百分比。任何超过20%的值都表明可能存在CPU资源瓶颈。获取“信号等待百分比”的脚本如下SELECTCAST(100.0*SUM(signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))AS[signalwaitpercent(%)],CAST(100.0*SUM(wait_time_ms-signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))AS[resourcewaits(%)]FROMsys.dm_os_wait_statsOPTION(RECOMPILE);注意signal_wait_time_ms的值是自上次重启服??务器后计算或累加的。由于是累加值，所以上面计算出的[signalwaitpercent(%)]的值是一个平均值，可能一段时间内不会发生变化。不适用于计算某个时间点或时间段之间的“信号等待百分比”。解决方法有两种：1：用下面的方法设置复位后，再计算“SignalWaitPercent”。不推荐使用此方法。因为这样的操作可能会影响其他的数据采集或者监控指标。DBCCSQLPERF('sys.dm_os_wait_stats',CLEAR);2：每隔一段时间（一分钟）采集指标signal_wait_time_ms和wait_time_ms的值，然后用后一个值减去前一个值，计算出“SignalWaitPercent”。另外，如果一个SQLServer实例下有多个用户数据库，有没有办法统计哪个用户数据库占用服务器CPU资源的比例？统计哪个用户数据库占用的CPU资源最多。当然，这只是大概的统计，不是很准确。WITHDB_CPU_StatsAS(SELECTpa.DatabaseID,DB_NAME(pa.DatabaseID)AS[DatabaseName],SUM(qs.total_worker_time/1000)AS[CPU_Time_Ms]FROMsys.dm_exec_query_statsASqsWITH(NOLOCK)CROSSAPPLY(SELECTCONVERT(INT,value)AS[DatabaseID]FROMsys.dm_attributes_plan(qs.plan_handle)WHEREattribute=N'dbid')ASpaGROUPBYDatabaseID)SELECTROW_NUMBER()OVER(ORDERBY[CPU_Time_Ms]DESC)AS[CPURank],[DatabaseName],[CPU_Time_Ms]AS[CPUTime(ms)],CAST([CPU_Time_Ms]]*1.0/SUM([CPU_Time_Ms])OVER()*100.0ASDECIMAL(5,2))AS[CPUPercent(%)]FROMDB_CPU_StatsWHEREDatabaseID<>1ORDERBY[CPURank]OPTION(RECOMPILE);总结：这里介绍一下如何通过SQLServer的一些DMV视图获取服务器CPU的各种指标数据的方法。它的优点是不需要在服务器上安装客户端（Agent）工具，一个SQL账号就可以收集收集CPU的各种指标数据。缺点也很明显，比如采集频率不能自定义。事实上，Solarwinds的DatabasePerformanceAnalyzer等工具就是通过这种方式来收集各种CPU指标的。优点和缺点。了解了这些知识点之后，你也可以在自己的监控工具中加入这些功能，来完善和增加一些监控功能。
