THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

Getting CPU Utilization Data from SQL Server

 

When I installed the SQL Server Performance Dashboard for the first time, perhaps a couple of years ago, the first thing that caught my attention was the nice graph in the main screen showing the system CPU utilization. Then I immediately wanted to know where this report was getting this CPU information from. A few minutes later I found the undocumented sys.dm_os_ring_buffers DMV.

 

clip_image002

 

Even when the sys.dm_os_ring_buffers DMV returns information about memory management, error handling data, and some other data about the state of the server, it is its scheduler monitor health records which provide the system CPU utilization information.

 

To get this data the ring_buffer_type field needs to be used to filter on the RING_BUFFER_SCHEDULER_MONITOR value as shown in the following query, which works on both SQL Server 2005 and SQL Server 2008.

 

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>%'

 

The information is stored in XML format and some sample output is shown here

 

clip_image004

 

One record is stored every minute up to a maximum of 256 records (if the instance has been running long enough, that is, more than 4 hours). Clicking on any of the links will take you to the XML editor and will show an entry similar to this

 

<Record id="1434" type="RING_BUFFER_SCHEDULER_MONITOR" time="244409400">

  <SchedulerMonitorEvent>

    <SystemHealth>

      <ProcessUtilization>44</ProcessUtilization>

      <SystemIdle>50</SystemIdle>

      <UserModeTime>337187500</UserModeTime>

      <KernelModeTime>197812500</KernelModeTime>

      <PageFaults>64252</PageFaults>

      <WorkingSetDelta>21770240</WorkingSetDelta>

      <MemoryUtilization>100</MemoryUtilization>

    </SystemHealth>

  </SchedulerMonitorEvent>

</Record>

 

The fields that show the CPU information are ProcessUtilization, which is the amount of CPU used by SQL Server, and SystemIdle, which is amount of idle CPU. The remaining percentage is the amount of CPU used by the other processes running on the server.

 

This is the query used by the Performance Dashboard using the XQuery value() method to get the required information from the XML record

 

declare @ts_now bigint

select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

     

select record_id,

      dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,

      SQLProcessUtilization,

      SystemIdle,

      100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization

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

 

Note that this query also uses the sys.dm_os_sys_info DMV. While testing on SQL Server 2008 I found that the cpu_ticks_in_ms column is no longer available on this DMV. So, if you want to run this code in this version of SQL Server just replace the second line of the code with this line

 

select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info;

 

Running the query will show something similar to this output

 

clip_image006

 

Finally, you could use this query (or the msdb.MS_PerfDashboard.usp_Main_GetCPUHistory stored procedure installed by the Performance Dashboard) to collect this CPU information periodically, for example, as a job running every 15 minutes.

 

Keep in mind that the Performance Dashboard is not needed in SQL Server 2008 as this version includes the new Data Collector, a feature that allows you to store the performance and diagnostics historic information of your SQL Server instances.

Published Sunday, July 26, 2009 10:19 PM by Ben Nevarez
Filed under: ,
New Comments to this post are disabled

About Ben Nevarez

Benjamin Nevarez is a SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at http://www.benjaminnevarez.com and he can also be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement