SQL Server 环形缓冲区(Ring Buffer) -- RING_BUFFER_SCHEDULER_MONITOR 获取SQL进程的CPU利用率
环形缓冲区存储了有关CPU利用率的信息。这些信息每分钟更新一次。所以你可以跟踪到4小时15分钟内给定时间点的CPU利用率。下面的输出显示了SQL实例的CPU利用率和其他活动进程的CPU利用率。这将帮助我们分析是否SQL Server进程占用大量CPU。
对于SQL Server 2005:
declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
select record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
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
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%') as x
) as y
order by record_id desc
对于SQL Server 2008:
declare @ts_now bigint
select @ts_now = ms_ticks from
sys.dm_os_sys_info
select record_id, dateadd (ms, (y.[timestamp] -@ts_now), GETDATE()) as EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
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
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%') as x
) as y
order by record_id desc
常用SQL脚本:
select
record
,CAST(record AS XML)
,CAST(record as XML).value('/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/MemoryUtilization[1]','int') as MemUtil
,CAST(record as XML).value('/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/PageFaults[1]','int') as PageFaults
,CAST(record as XML).value('/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/WorkingSetDelta[1]','int') as workingSetDelta
,CAST(record as XML).value('/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/KernelModeTime[1]','int') as KernelModeTime
,CAST(record as XML).value('/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/UserModeTime[1]','bigint') as UserModeTime
,CAST(record as XML).value('/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/ProcessUtilization[1]','int') as ProcessUtilization
,CAST(record as XML).value('/Record[1]/SchedulerMonitorEvent[1]/SystemHealth[1]/SystemIdle[1]','int') as SystemIdle
from sys.dm_os_ring_buffers
where
ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'