Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A DMV a Day – Day 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] 
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;

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.

Comments

Posted by Anonymous on 21 April 2010

Pingback from  Dew Drop &ndash; April 21, 2010 | Alvin Ashcraft&#039;s Morning Dew

Posted by Dukagjin Maloku on 21 April 2010

I like it, nice info about CPU ...! It is very nice that the Event Time is related to SELECT TOP (minutes), but the max value that can be use is 256, so with this nice DMV you can check what happened between 256 minutes with your CPU(s)!

Posted by Jason Brimhall on 21 April 2010

Thanks Glenn, I think this would definitely be more useful than perfmon for many situations.

Posted by JimBama08 on 26 April 2010

Do you have a version of this query that runs on SQL 2005?

Posted by Glenn Berry on 26 April 2010

This version works on SQL Server 2005

-- Get CPU Utilization History (SQL 2005 Only)

DECLARE @ts_now bigint;

SET @ts_now = (SELECT cpu_ticks / CONVERT(float, cpu_ticks_in_ms) 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]

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;

Leave a Comment

Please register or log in to leave a comment.