MSSQLWIKI

Karthick P.K on SQL Server

How to find SQL Server and system CPU usage history :

Posted by Karthick P.K on November 30, 2010

SQL Server and system CPU usage history can be obtained from sys.dm_os_ring_buffers  using below query

Note: For troubleshooting  high CPU usage in SQL Server follow http://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/

SQL Server 2005

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime, 

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM 

(

  SELECT 

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record 

    FROM sys.dm_os_ring_buffers 

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%<SystemHealth>%') AS t

) AS t

ORDER BY record_id desc

 

SQL Server 2008

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime, 

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM 

(

  SELECT 

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record 

    FROM sys.dm_os_ring_buffers 

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%<SystemHealth>%') AS t

) AS t

ORDER BY record_id desc

About these ads

8 Responses to “How to find SQL Server and system CPU usage history :”

  1. Jude said

    We’re a bunch of volunteers and opening a new scheme in our community. Your site provided us with valuable info to work on. You’ve done
    an impressive process and our entire group will be thankful to you.

  2. That is very interesting, You are an overly skilled blogger.
    I’ve joined your feed and stay up for searching for extra of your wonderful post. Also, I’ve shared your web site in my social networks

  3. You ought to be a part of a contest for one of the best sites on the internet.
    I’m going to highly recommend this website!

  4. Good day! I just wish to give a huge thumbs up for the good information you have here on
    this post. I will be coming back to your weblog for more soon.

  5. kranthi said

    Hi Karthi

    What are the counters we need to look into from the script output and what are the thresholdvalues

  6. srinivas d said

    Hey Karthick,
    what does the column record_id refer to ?

    Would it be possible to get the utilization history per database ?

    /srinivas

  7. […] How to find SQL Server and system CPU usage history […]

  8. Margherita said

    Nice respond in return of this issue with real arguments and explaining everything regarding that.

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

 
Follow

Get every new post delivered to your Inbox.

Join 2,135 other followers

%d bloggers like this: