怎么监控SQL Server服务器CPU的各项指标

发布时间：2022-06-21 11:21:08 所属栏目：系统来源：互联网

导读：如何采集SQL Server数据库服务器上的CPU的一些指标呢?我们知道一些监控工具(例如Zabbix)可以很简单、轻松的获取CPU利用率等指标，但是Zabbix需要在服务器上安装客户端，那么能否通过SQL账号，直接获取例如CPU利用率，SQL Server数据库实例CPU利用率(Instance

　　如何采集SQL Server数据库服务器上的CPU的一些指标呢?我们知道一些监控工具(例如Zabbix)可以很简单、轻松的获取CPU利用率等指标，但是Zabbix需要在服务器上安装客户端，那么能否通过SQL账号，直接获取例如CPU利用率，SQL Server数据库实例CPU利用率(Instance CPU Utilization)、CPU Signal Waits这些指标呢?如下图所示：

　　

　　在SQL Server中，其实有个没有文档的DMV视图sys.dm_os_ring_buffers，我们可以从这个DMV中获取服务器CPU利用率，数据库实例的CPU利用率，脚本如下

　　复制
　　 /**************************************************************************************************************
　　    --脚本名称：      get_cpu_utilization_his.sql
　　    --脚本作者 :
　　    --创建日期 :       2017-05-28
　　***************************************************************************************************************
　　    脚本功能    ：       查看SQL Server数据库实例服务器的CPU利用率信息
　　***************************************************************************************************************
　　    注意事项    :       1: 默认情况下，从sys.dm_os_ring_buffers中只能获取最近的256分钟数据（间隔为1分钟），这个
　　                           DMV没有文档（undocumented）,所以要获取整个服务器的cpu利用率，必须定期采集数据才行。
　　                       2: cpu_ticks指定当前的 CPU 时钟周期计数。 CPU 时钟周期数是从处理器的 RDTSC 计数器获得的。它是一个仅增加的数字。不可为 Null。ms_ticks指定自从计算机启动以来的毫秒数。不可为 Null。
　　
　　                      变量@ts_now可能存在毫秒级别的差异.
　　***************************************************************************************************************
　　                        此脚本支持SQL Server 2008、2012、2014、2016、2017
　　***************************************************************************************************************
　　    更新记录    ：      2017-05-28 创建此脚本
　　                      2019-10-15 加入OS CPU Utilization(%)
　　***************************************************************************************************************/
　　DECLARE @ts_now BIGINT;
　　SET @ts_now= ( SELECT cpu_ticks / ( cpu_ticks / ms_ticks )
　　                FROM    sys.dm_os_sys_info WITH ( NOLOCK )
　　              );
　　
　　
　　 SELECT
　　        @@SERVERNAME AS [Server Name]
　　       ,DATEADD(ms, -1 * (@ts_now - [timestamp] ), GETDATE()) AS [Event Time]
　　       ,SQLProcessUtilization                                  AS [SQL Server Process CPU Utilization(%)]
　　       ,100 - SystemIdle - SQLProcessUtilization               AS [Other Process CPU Utilization(%)]
　　       ,100 - SystemIdle                                       AS [OS CPU Utilization(%)]
　　       ,SystemIdle                                             AS [System Idle Process(%)]
　　
　　 FROM   ( SELECT    record.value('(./Record/@id)[1]', 'int') AS record_id ,
　　                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',
　　                                 'int') AS [SystemIdle] ,
　　                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
　　                                 'int') AS [SQLProcessUtilization] ,
　　                    [timestamp]
　　          FROM      ( SELECT    [timestamp] ,
　　                                CONVERT(XML, record) AS [record]
　　                      FROM      sys.dm_os_ring_buffers WITH ( NOLOCK )
　　                      WHERE     ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
　　                                AND record LIKE N'%<SystemHealth>%'
　　                    ) AS x
　　        ) AS y
　　 ORDER BY record_id DESC
　　 OPTION ( RECOMPILE );
　　1.
　　2.
　　3.
　　4.
　　5.
　　6.
　　7.
　　8.
　　9.
　　10.
　　11.
　　12.
　　13.
　　14.
　　15.
　　16.
　　17.
　　18.
　　19.
　　20.
　　21.
　　22.
　　23.
　　24.
　　25.
　　26.
　　27.
　　28.
　　29.
　　30.
　　31.
　　32.
　　33.
　　34.
　　35.
　　36.
　　37.
　　38.
　　39.
　　40.
　　41.
　　42.
　　43.
　　44.
　　45.
　　46.
　　47.
　　sys.dm_os_ring_buffers是一个没有文档函数，里面仅仅保存了256分钟的CPU利用率数据，一分钟一条数据。但是也没有其它地方可以设置，延长保存更长时间的数据，所以如果要获取历史的CPU利用率等指标数据，只能通过Python或作业定期采集。下面是Python中要用到的表和脚本。

　　复制
　　USE YourSQLDba;
　　GO
　　IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE type='u' AND name='SERVER_CPU_INFO')
　　BEGIN
　　      CREATE TABLE dbo.SERVER_CPU_INFO
　　      (
　　         [server_name]    NVARCHAR(64),
　　         [event_time]    DATETIME,
　　         [sqlserver_cpu_utilization] FLOAT NOT NULL,
　　         [other_cpu_utilization]     FLOAT NOT NULL,
　　         [os_cpu_utilization] FLOAT NOT NULL,
　　         [idle_cpu_utilization]     FLOAT NOT NULL,
　　         CONSTRAINT PK_SERVER_CPU_INFO PRIMARY KEY([server_name], [event_time])
　　      );
　　END
　　GO
　　
　　EXEC sys.sp_addextendedproperty @name = N'MS_Description',
　　    @value = N'服务器CPU利用率信息表', @level0type = N'SCHEMA', @level0name = N'dbo',
　　    @level1type = N'TABLE', @level1name = N'SERVER_CPU_INFO';
　　GO
　　EXEC sys.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';
　　GO
　　EXEC sys.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';
　　GO
　　EXEC sys.sp_addextendedproperty @name = N'MS_Description',
　　    @value = N'SQL Server实例占用CPU百分比', @level0type = N'SCHEMA',
　　    @level0name = N'dbo', @level1type = N'TABLE',
　　    @level1name = N'SERVER_CPU_INFO', @level2type = N'COLUMN',
　　    @level2name = N'sqlserver_cpu_utilization';
　　GO
　　EXEC sys.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';
　　GO
　　EXEC sys.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';
　　GO
　　EXEC sys.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';
　　GO
　　
　　 MERGE INTO dbo.SERVER_CPU_INFO S
　　 USING
　　 (
　　 SELECT
　　   @@SERVERNAME AS [Server Name]
　　     ,DATEADD(ms, -1 * ( (SELECT cpu_ticks / ( cpu_ticks / ms_ticks ) FROM    sys.dm_os_sys_info WITH ( NOLOCK ))
　　        - [timestamp] ), GETDATE()) AS [Event Time]
　　     ,SQLProcessUtilization                                  AS [SQL Server Process CPU Utilization(%)]
　　     ,100 - SystemIdle                                       AS [OS CPU Utilization(%)]
　　     ,SystemIdle                                             AS [System Idle Process(%)]
　　     ,100 - SystemIdle - SQLProcessUtilization               AS [Other Process CPU Utilization(%)]
　　  FROM   ( SELECT    record.value('(./Record/@id)[1]', 'int') AS record_id ,
　　      record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',
　　          'int') AS [SystemIdle] ,
　　      record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
　　          'int') AS [SQLProcessUtilization] ,
　　      [timestamp]
　　     FROM      ( SELECT    [timestamp] ,
　　                           CONVERT(XML, record) AS [record]
　　                 FROM      sys.dm_os_ring_buffers WITH ( NOLOCK )
　　                 WHERE     ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
　　         AND record LIKE N'%<SystemHealth>%'
　　      ) AS x
　　   ) AS y
　　 ) T
　　 --ON (T.[Server Name] = S.server_name AND T.[Event Time] = s.event_time)
　　 --注意：由于计算[Event Time]存在偏差，可能导致出现重复记录，只能将其转化精确到分.
　　 ON (T.[Server Name] = S.server_name AND CONVERT(VARCHAR(16),T.[Event Time],120) = CONVERT(VARCHAR(16),s.event_time,120))
　　 WHEN NOT MATCHED THEN
　　 INSERT (
　　          [server_name]
　　         ,[event_time]
　　         ,[sqlserver_cpu_utilization]
　　         ,[os_cpu_utilization]
　　         ,[idle_cpu_utilization]
　　         ,[other_cpu_utilization]
　　
　　    )
　　  VALUES (   T.[Server Name]
　　            ,CONVERT(VARCHAR(19),T.[Event Time],120)
　　            ,T.[SQL Server Process CPU Utilization(%)]
　　            ,T.[OS CPU Utilization(%)]
　　            ,T.[System Idle Process(%)]
　　            ,T.[Other Process CPU Utilization(%)]
　　    );
　　有时候项目经理会跟你说，我们数据库服务器CPU压力大不大，存不存在CPU资源瓶颈?你怎么判断呢?我们不能仅仅根据服务器CPU的利用率来判断，毕竟业务高峰期间，服务器CPU利用率本来可能就比较高，一般而言，我们还需要通过指标“Signal Wait Percent”来判定CPU是否存在瓶颈，通过这个指标判断CPU的瓶颈是否影响了数据库性能。在了解这个指标前，我们先要了解“CPU signal wait time”这个指标。这个指标是啥呢?它指进程或线程从发出信号到开始运行的时间差，在等待运行队列中时间开销，是单纯的CPU等待。

　　而指标“Signal Wait Percent”它描述了指令等待CPU资源的时间占总时间的百分比。如果“Signal Wait Percent”较高的话，这可能表明CPU已被过度使用，从而迫使SQL Server进程进入任务等待。如果超过20%，说明CPU资源紧张，存在瓶颈。

　　Signal Wait Percent

　　The signal wait percentage shows the percentage of overall time that sessions are waiting for a CPU to become available. Anything over 20% would indicate that there is a possible CPU resource bottleneck.

　　获取“Signal Wait Percent”的脚本如下

　　复制
　　SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,
　　                                                              2)) AS [signal wait percent(%)] ,
　　        CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms)
　　        / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [resource waits(%)]
　　FROM    sys.dm_os_wait_stats
　　OPTION ( RECOMPILE );
　　1.
　　2.
　　3.
　　4.
　　5.
　　6.
　　注意，signal_wait_time_ms这些值是从服务器的最后一次重新启动后开始计算或累加的，由于是一个累加值，所以，上面计算的[signal wait percent(%)]的值是一个平均值，一段时间内可能不会有变化，它不适合计算某个时间点或时间段之间的“Signal Wait Percent”，可以有下面两种方法解决：

　　1：使用以下方法将其重置后，然后计算“Signal Wait Percent”。不推荐使用这种方法。因为这类操作可能会影响其他数据采集或监控指标。
　　DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
　　2：间隔(一分钟)采集一次指标signal_wait_time_ms 和wait_time_ms的值，然后用后面一次的值减去上面一次的值，从而可以计算一分钟内的“Signal Wait Percent”。
　　另外，如果一个SQL Server实例下，有多个用户数据库，那么有没有方法统计那个用户数据库消耗了服务器CPU资源的比例呢?统计那个用户数据库消耗的CPU资源最多。当然这个只是大概统计，不是非常精准。

　　复制
　　WITH    DB_CPU_Stats
　　          AS ( SELECT   pa.DatabaseID ,
　　                        DB_NAME(pa.DatabaseID) AS [Database Name] ,
　　                        SUM(qs.total_worker_time / 1000) AS [CPU_Time_Ms]
　　               FROM     sys.dm_exec_query_stats AS qs WITH ( NOLOCK )
　　                        CROSS APPLY ( SELECT    CONVERT(INT, value) AS [DatabaseID]
　　                                      FROM      sys.dm_exec_plan_attributes(qs.plan_handle)
　　                                      WHERE     attribute = N'dbid'
　　                                    ) AS pa
　　               GROUP BY DatabaseID
　　             )
　　    SELECT ROW_NUMBER() OVER ( ORDER BY [CPU_Time_Ms] DESC ) AS [CPU Rank] ,
　　            [Database Name] ,
　　            [CPU_Time_Ms] AS [CPU Time (ms)] ,
　　            CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER ( ) * 100.0 AS DECIMAL(5,
　　                                                              2)) AS [CPU Percent(%)]
　　    FROM    DB_CPU_Stats
　　    WHERE   DatabaseID <> 1
　　    ORDER BY [CPU Rank]
　　OPTION ( RECOMPILE );
　　总结：

　　

　　这里我们介绍了如何通过SQL Server的一些DMV视图获取服务器CPU的各项指标数据的一些方法，它的优点是不用在服务器上安装客户端(Agent)工具，一个SQL账号即可采集收集CPU各项指标数据。不足也比较明显，例如，采集频率无法定制。其实像Solarwinds的Database Performance Analyzer等工具就是用这种方式采集CPU各项指标。各有利弊。了解了这些知识点，你也可以在自己的监控工具中加入这些功能，完善、增加一些监控功能。

（编辑：我爱制作网_潮州站长网）

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

编写 Bash 脚本所需了	电脑死机重装系统怎么
华硕笔记本如何重装系	大白菜超级U盘启动如何