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.

About these ads
This entry was posted in SQL Server 2008. Bookmark the permalink.

2 Responses to A DMV a Day – Day 21

  1. Fabrício says:

    Have you a version for SQL Server 2005?Thanks.

  2. M.István says:

    This very useful script has a litle bug: the calculated column [Event Time] from the result set is always in the future.You can verify very easy if you add a new return colum egal with GetDate().The same problem is with the SQLServer2005_PerformanceDashboard.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s