How to understand the RING_BUFFER_SCHEDULE_MONITOR

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)[1]', 'int') AS [ProcessUtilization],    
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle %],   
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/UserModeTime) [1]', 'bigint') AS [UserModeTime],   
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime) [1]', 'bigint') AS [KernelModeTime],    
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/PageFaults) [1]', 'bigint') AS [PageFaults],   
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta) [1]', 'bigint')/1024 AS [WorkingSetDelta],   
x.value('(//Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization) [1]', 'bigint') AS [MemoryUtilization (%workingset)],   
x.value('(//Record/@time)[1]', '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())

---

 Read More SQL Server health monitoring

Troubleshoot SQL Security Error Ring Buffer Recorded -

SQL Server – Daily Health Check Script with Powershell - SQL ...

Century of the System and managing complexity - SQL Server DBA