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.

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

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.