本文转载自微信公众号「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利用率,脚本如下
-
- 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'%
%' - ) AS x
- ) AS y
- ORDER BY record_id DESC
- OPTION ( RECOMPILE );
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'%
%' - ) 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 );
注意,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各项指标。各有利弊。了解了这些知识点,你也可以在自己的监控工具中加入这些功能,完善、增加一些监控功能。