The RING_BUFFER_SCHEDULE_MONITOR has been around since SQL Server 2005. It remains a great way of obtaining system CPU utilization information. This post defines and offers some usages for System Idle, User Mode Time, Kernel Mode Time and page faults returned by the RING_BUFFER_SCHEDULE_MONITOR
Most sample queries have the same basic format . I have an example , further down the blog post. The trick is interpreting the figures ,and correlating to tasks . You can use these correlations to drill down into root causes.
The key point is to prioritise understanding what is “normal” for the particular SQL Server you’re troubleshooting. Have you established a baseline? Using a baseline, it is easier to confirm whether the figures deviate from the normal behaviour
CPU is consumed in two different modes:
1) User Mode
2) Kernel Mode
Keep that in mind as you analyse the data from RING_BUFFER_SCHEDULE_MONITOR.
Some definitions and helpful information on columns returned
System Idle - When the "System Idle Process" is at 100%, that means nothing is using your CPU resources
User Mode Time - If “% User Time” is high then there is something consuming the user mode of SQL Server. Check for:
1. High CPU caused by queries - SQL Server – Performance Top Queries by Total CPU Time
2. CPU consumed by system tasks
3. Increased Query Compilation\Recompilation - SQL Server - How to improve Execution Plan Reuse - SQL Server DBA
Kernel Mode Time – If you observe consistent system time greater than 15%, or the system time cosnsistently is greater than usermodetime, analyse what is running on the Operating System to cause the overutilization. The consequence is application (SQL Server ) threads won’t be able to use the CPU .
Page Faults - “A page fault occurs when a program requests an address on a page that is not in the current set of memory resident pages.
The program is set to a Wait State when a page fault occurs. The OS searches for the address on the disk. When it finds the address it moves it to some free RAM. When completed , the program continues its execution
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime, DATEADD (ms, a.[Record Time] - sys.ms_ticks, GETDATE()) AS Notification_time, a.* , sys.ms_ticks AS [Current Time] FROM (SELECT x.value('(//Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', 'int') AS [ProcessUtilization], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int') AS [SystemIdle %], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/UserModeTime) ', 'bigint') AS [UserModeTime], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime) ', 'bigint') AS [KernelModeTime], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/PageFaults) ', 'bigint') AS [PageFaults], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta) ', 'bigint')/1024 AS [WorkingSetDelta], x.value('(//Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization) ', 'bigint') AS [MemoryUtilization (%workingset)], x.value('(//Record/@time)', 'bigint') AS [Record Time] FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR') AS R(x)) a CROSS JOIN sys.dm_os_sys_info sys ORDER BY DATEADD (ms, a.[Record Time] - sys.ms_ticks, GETDATE())