How To Get SQL Server CPU Utilization From a Query

I like to use DMV and other T-SQL queries to quickly and easily get information about how my database servers are running. I have written, used, and shared many useful queries for finding out quite a bit about what was happening on SQL Server, but I never knew how to easily get the CPU Utilization of SQL Server from a query within SQL Server.

This post from the SQL Server Premier Field Engineer Blog gave me the inspiration to try to figure it out. Then Ben Nevarez had an excellent post that really explained it.

I decided to slightly modify and cleanup the Microsoft query from Ben’s article to get what you see below:

    -- Get CPU Utilization History (SQL 2008 Only)
    DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 

    SELECT TOP(10) 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;
    
    
    
    -- Get CPU Utilization History (SQL 2005 Only)
    DECLARE @ts_now bigint; 
    SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info 

    SELECT TOP(10) 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;

These let you get the historical CPU Utilization of SQL Server, at one minute intervals, going back ten minutes, which gives you a pretty good idea of your recent CPU history. It is great to be able to do this from a T-SQL query instead of having to use WMI or a CLR assembly.

Technorati Tags:

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

7 Responses to How To Get SQL Server CPU Utilization From a Query

  1. Jim says:

    I tried your sql for 2005 and am getting an error. Can you assist ?Msg 1934, Level 16, State 1, Server LA-LL-SQL1, Line 4SELECT failed because the following SET options have incorrect settings: \’QUOTED_IDENTIFIER\’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

  2. Glenn says:

    Try adding this command just before you run the query:SET QUOTED_IDENTIFIER ON;

  3. Jim says:

    Excellent GlennThanksJim

  4. guru charan says:

    Nice queries.. I am as well trying to build script library that are categorized based on performance problems.. here are some of them for solving high Cpu performance problems (only on server 2005 and sql server 2008)http://www.sqlfundas.com/post/2009/10/20/Diagnostic-Queries-to-Troubleshoot-High-CPU-performance-problem-in-SQL-Server-2005-or-2008.aspx

  5. Mohan says:

    Great!! It is very very useful for me

  6. John Couch says:

    Glenn, I believe the EventTime value being listed is incorrect. If you capture the data from a single execution, then run the same thing again about 5 sec later you can tell the same event is listed but with a different EventTime value. You can verify that by including Record_id in the resultset as well.

    • John Couch says:

      So, after looking at it further, it would appear that the millisecond value is what is inconsistent. The hour/min/sec remain unchanged each execution. The millisecond value however fluctuates all over. taking the floor value of the milliseconds and just setting it to 000 alleviates the problem.

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