Printed 2017/07/28 02:08PM

A DMV a Day – Day 21

By GlennBerry, 2010/04/21

The DMV for Day 21 is sys.dm_os_ring_buffers, which is helpfully NOT described by BOL as:

The following SQL Server Operating System–related dynamic management views are Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Ooh, that sounds scary!  Well despite that warning and lack of documentation in BOL, there are multiple blog posts from Microsoft employees that show examples of using sys.dm_os_ring_buffers.  This DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Get CPU Utilization History for last 30 minutes (in one minute intervals)
-- This version works with SQL Server 2008 and SQL Server 2008 R2 only
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 

SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization], 
               SystemIdle AS [System Idle Process], 
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], 
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
            AS [SystemIdle], 
            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;

This query gives you your CPU utilization history (in one minute increments) over the last 30 minutes, both for the SQL Server process and for all the other processes (summed together) on your database server. The query actually subtracts the SystemIdle value and the SQL Server process value from 100 to arrive at the value for all other processes on the server. This is very handy to easily see your recent CPU utilization history for the server as a whole, for SQL Server and for other processes that are running on your database server (such as management software). Even though the granularity is only one minute, I like to be able to see this from T-SQL rather than having to look at PerfMon or use WMI to get CPU utilization information. In my experimentation, you can only retrieve 256 minutes worth of data from this query.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.