获取SQL Server CPU使用数据的脚本

Getting CPU Utilization Data from SQL Server 

 

from: http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx

 

 

 

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.

 

 

 

 

 

 

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

 

 

 

 

 

 

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

 

 

 

 

 

 

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.