如何监控SQL Server服务器CPU的各项指标

服务器
这里我们介绍了如何通过SQL Server的一些DMV视图获取服务器CPU的各项指标数据的一些方法,它的优点是不用在服务器上安装客户端(Agent)工具,一个SQL账号即可采集收集CPU各项指标数据。

[[386606]]

本文转载自微信公众号「DBA闲思杂想录」,作者潇湘隐者。转载本文请联系DBA闲思杂想录公众号。

如何采集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利用率,脚本如下

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

sys.dm_os_ring_buffers是一个没有文档函数,里面仅仅保存了256分钟的CPU利用率数据,一分钟一条数据。但是也没有其它地方可以设置,延长保存更长时间的数据,所以如果要获取历史的CPU利用率等指标数据,只能通过Python或作业定期采集。下面是Python中要用到的表和脚本。

  1. USE YourSQLDba; 
  2. GO 
  3. IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE type='u' AND name='SERVER_CPU_INFO'
  4. BEGIN 
  5.       CREATE TABLE dbo.SERVER_CPU_INFO 
  6.       ( 
  7.          [server_name]    NVARCHAR(64), 
  8.          [event_time]    DATETIME, 
  9.          [sqlserver_cpu_utilization] FLOAT NOT NULL
  10.          [other_cpu_utilization]     FLOAT NOT NULL
  11.          [os_cpu_utilization]  FLOAT NOT NULL
  12.          [idle_cpu_utilization]     FLOAT NOT NULL
  13.          CONSTRAINT PK_SERVER_CPU_INFO PRIMARY KEY([server_name], [event_time]) 
  14.       ); 
  15. END 
  16. GO 
  17.  
  18. EXEC sys.sp_addextendedproperty @name = N'MS_Description'
  19.     @value = N'服务器CPU利用率信息表', @level0type = N'SCHEMA', @level0name = N'dbo'
  20.     @level1type = N'TABLE', @level1name = N'SERVER_CPU_INFO'
  21. GO 
  22. EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'服务器名称'
  23.     @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE'
  24.     @level1name = N'SERVER_CPU_INFO', @level2type = N'COLUMN'
  25.     @level2name = N'server_name'
  26. GO 
  27. EXEC sys.sp_addextendedproperty @name = N'MS_Description'
  28.     @value = N'事件发生日期时间', @level0type = N'SCHEMA', @level0name = N'dbo'
  29.     @level1type = N'TABLE', @level1name = N'SERVER_CPU_INFO'
  30.     @level2type = N'COLUMN', @level2name = N'event_time'
  31. GO 
  32. EXEC sys.sp_addextendedproperty @name = N'MS_Description'
  33.     @value = N'SQL Server实例占用CPU百分比', @level0type = N'SCHEMA'
  34.     @level0name = N'dbo', @level1type = N'TABLE'
  35.     @level1name = N'SERVER_CPU_INFO', @level2type = N'COLUMN'
  36.     @level2name = N'sqlserver_cpu_utilization'
  37. GO 
  38. EXEC sys.sp_addextendedproperty @name = N'MS_Description'
  39.     @value = N'服务器CPU利用率', @level0type = N'SCHEMA', @level0name = N'dbo'
  40.     @level1type = N'TABLE', @level1name = N'SERVER_CPU_INFO'
  41.     @level2type = N'COLUMN', @level2name = N'os_cpu_utilization'
  42. GO 
  43. EXEC sys.sp_addextendedproperty @name = N'MS_Description'
  44.     @value = N'空闲CPU利用率', @level0type = N'SCHEMA', @level0name = N'dbo'
  45.     @level1type = N'TABLE', @level1name = N'SERVER_CPU_INFO'
  46.     @level2type = N'COLUMN', @level2name = N'idle_cpu_utilization'
  47. GO 
  48. EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'其它进程利用率'
  49.     @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE'
  50.     @level1name = N'SERVER_CPU_INFO', @level2type = N'COLUMN'
  51.     @level2name = N'other_cpu_utilization'
  52. GO 
  53.  
  54.  MERGE INTO dbo.SERVER_CPU_INFO S 
  55.  USING 
  56.  ( 
  57.  SELECT  
  58.    @@SERVERNAME AS [Server Name]  
  59.      ,DATEADD(ms, -1 * ( (SELECT  cpu_ticks / ( cpu_ticks / ms_ticks ) FROM    sys.dm_os_sys_info WITH ( NOLOCK ))  
  60.         - [timestamp] ), GETDATE()) AS [Event Time
  61.      ,SQLProcessUtilization                                  AS [SQL Server Process CPU Utilization(%)]  
  62.      ,100 - SystemIdle                                       AS [OS CPU Utilization(%)] 
  63.      ,SystemIdle                                             AS [System Idle Process(%)]  
  64.      ,100 - SystemIdle - SQLProcessUtilization               AS [Other Process CPU Utilization(%)]  
  65.   FROM   ( SELECT    record.value('(./Record/@id)[1]''int'AS record_id , 
  66.       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]'
  67.           'int'AS [SystemIdle] , 
  68.       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]'
  69.           'int'AS [SQLProcessUtilization] , 
  70.       [timestamp
  71.      FROM      ( SELECT    [timestamp] , 
  72.                            CONVERT(XML, record) AS [record] 
  73.                  FROM      sys.dm_os_ring_buffers WITH ( NOLOCK )  
  74.                  WHERE     ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
  75.          AND record LIKE N'%<SystemHealth>%' 
  76.       ) AS x 
  77.    ) AS y  
  78.  ) T 
  79.  --ON (T.[Server Name] = S.server_name AND T.[Event Time] = s.event_time)   
  80.  --注意:由于计算[Event Time]存在偏差,可能导致出现重复记录,只能将其转化精确到分. 
  81.  ON (T.[Server Name] = S.server_name AND CONVERT(VARCHAR(16),T.[Event Time],120) = CONVERT(VARCHAR(16),s.event_time,120)) 
  82.  WHEN NOT MATCHED THEN 
  83.  INSERT  (  
  84.           [server_name]              
  85.          ,[event_time]   
  86.          ,[sqlserver_cpu_utilization] 
  87.          ,[os_cpu_utilization]  
  88.          ,[idle_cpu_utilization]  
  89.          ,[other_cpu_utilization] 
  90.      
  91.     ) 
  92.   VALUES (   T.[Server Name]  
  93.             ,CONVERT(VARCHAR(19),T.[Event Time],120) 
  94.             ,T.[SQL Server Process CPU Utilization(%)]  
  95.             ,T.[OS CPU Utilization(%)] 
  96.             ,T.[System Idle Process(%)]  
  97.             ,T.[Other Process CPU Utilization(%)]  
  98.     ); 

有时候项目经理会跟你说,我们数据库服务器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”的脚本如下

  1. SELECT  CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 
  2.                                                               2)) AS [signal wait percent(%)] , 
  3.         CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) 
  4.         / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [resource waits(%)] 
  5. FROM    sys.dm_os_wait_stats 
  6. OPTION  ( RECOMPILE ); 

注意,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资源最多。当然这个只是大概统计,不是非常精准。

  1. WITH    DB_CPU_Stats 
  2.           AS ( SELECT   pa.DatabaseID , 
  3.                         DB_NAME(pa.DatabaseID) AS [Database Name] , 
  4.                         SUM(qs.total_worker_time / 1000) AS [CPU_Time_Ms] 
  5.                FROM     sys.dm_exec_query_stats AS qs WITH ( NOLOCK ) 
  6.                         CROSS APPLY ( SELECT    CONVERT(INT, value) AS [DatabaseID] 
  7.                                       FROM      sys.dm_exec_plan_attributes(qs.plan_handle) 
  8.                                       WHERE     attribute = N'dbid' 
  9.                                     ) AS pa 
  10.                GROUP BY DatabaseID 
  11.              ) 
  12.     SELECT  ROW_NUMBER() OVER ( ORDER BY [CPU_Time_Ms] DESC ) AS [CPU Rank] , 
  13.             [Database Name] , 
  14.             [CPU_Time_Ms] AS [CPU Time (ms)] , 
  15.             CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER ( ) * 100.0 AS DECIMAL(5, 
  16.                                                               2)) AS [CPU Percent(%)] 
  17.     FROM    DB_CPU_Stats 
  18.     WHERE   DatabaseID <> 1  
  19.     ORDER BY [CPU Rank] 
  20. OPTION  ( RECOMPILE ); 

总结:

 

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

 

责任编辑:武晓燕 来源: DBA闲思杂想录
相关推荐

2009-04-03 15:14:42

微软优化SQL Server

2018-11-22 08:44:02

SQL Server服务器工具

2010-07-14 09:52:50

SQL Server服

2023-09-06 08:46:47

2010-11-08 11:53:16

2010-11-09 14:47:46

SQL Server跨

2010-10-20 15:53:37

SQL Server服

2010-09-08 15:14:36

2020-06-07 11:54:34

Linux服务器命令

2020-10-09 07:00:00

无服务器应用监控架构

2019-06-13 17:15:30

监控Linux服务器

2011-04-02 10:33:40

SQL server服务器内存

2010-09-14 13:37:19

sql server备

2014-04-03 16:36:46

Ubuntu ServCacti服务器监控

2010-09-02 11:20:47

SQL删除

2010-10-22 13:56:41

SQL Server服

2010-09-01 17:07:08

Sql删除服务器

2011-09-15 13:24:15

SQL Server性能监视

2011-03-24 10:19:58

SQL Server2CPU性能监控

2011-09-13 09:35:37

SQL Server集群
点赞
收藏

51CTO技术栈公众号