Ring Buffer Monitoring CPU – What do those values mean?
February 24, 2020
Here is a sample output of XML from sys.dm_os_ring_buffers where WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’. What do those XML elements mean? In order to monitor CPU usages, you need to understand what each element means so you can use the values. I will explain each one in this blog post.
<Record id = "38446" type ="RING_BUFFER_SCHEDULER_MONITOR" time ="2313297649"> <SchedulerMonitorEvent> <SystemHealth> <ProcessUtilization>2</ProcessUtilization> <SystemIdle>91</SystemIdle> <UserModeTime>310468750</UserModeTime> <KernelModeTime>35781250</KernelModeTime> <PageFaults>7004</PageFaults> <WorkingSetDelta>0</WorkingSetDelta> <MemoryUtilization>100</MemoryUtilization> </SystemHealth> </SchedulerMonitorEvent>
- ProcessUtilization Indicates the amount of CPU SQL Server was using at the time of the snapshot.
- SystemIdle Amount of Idle CPU that nothing is using. Available for any process that requires CPU.
- (100 minus ProcessUtilization minus System Idle) CPU being used by processes other than SQL Server.
- UserModeTime Indicates the amount of CPU worker thread (Running in user mode) used during the period it did not yield. You need to divide this value by 10,000 to get time in milliseconds
- KernelModeTime Indicates the amount of CPU worker thread (Running in Windows kernel) used during the period it did not yield. You need to divide this value by 10,000 to get time in milliseconds.
- PageFaults – Number of page faults at the time of the snapshot. A page fault occurs when a program requests an address on a page that is not in the current set of memory-resident pages.
- WorkingSetDelta Difference in working set between last and current snapshot.
- MemoryUtilization Indicates the percentage of memory SQL Server is using based on max server memory (MB) setting. 100% is normal in this case as SQL OS is based on a greedy algorithm. It will consume all memory unless it is forced to give up memory due to external factors.
Few additional resources about monitoring CPU usage using sys.dm_os_ring_buffers:
- SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer i…by Denzi Ribeiro
- How to understand the RING_BUFFER_SCHEDULE_MONITOR by Jack Vambas