Performance - Get CPU Usage for Last 4 Hours

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        25 Aug 2009

Description

Retrieves the CPU percentage utilization for the last 4 hours in the following categories:
  • SQL Server 
  • Idle
  • Other Processes 
This data is retrieved from one of the default extended events traces (system health) which were introduced in SQL Server 2005.

There are slightly different versions for SQL 2005 and SQL 2008 onward due to a minor change in a DMV.  I did try and make the change dynamic in one SP, but time was short and I didn't feel it necessary to delve in to parameterized EXECUTE_SQL statements.

Code

SQL Server 2005:


DROP FUNCTION dbo.udf_GetProcessorUsageforLast4Hours2005
GO

CREATE FUNCTION dbo.udf_GetProcessorUsageforLast4Hours2005 ()
RETURNS @ProcessorUsageforLast4Hours2005 TABLE
  
(
      
Record_ID                   INT,
      
EventTime                   DATETIME,
      
SQLProcessUtilization       DECIMAL(18,2),
      
SystemIdle                  DECIMAL(18,2),
      
OtherProcessUtilization     DECIMAL(18,2)
   )
AS
/***************************************************************
Purpose:   To retrieve details of processor usage from the last 4 hours.
           Uses the SystemHealth exteneded event ring buffer
           Valid for SQL 2005 only as the structure of the sys.dm_os_sys_info
           view changes from SQL 2008          

           The following DMVs are used:
               sys.dm_os_sys_info      - contains OS version information
               sys.dm_os_ring_buffers  - contains a record of all currently active ring bufers and their contents        
          
Author:        Unknown
History:   25 Aug 2009

****************************************************************/

BEGIN

   DECLARE
      
@ProductVersionText         VARCHAR(20),
      
@ProductVersionInt          INT,
      
@ts_now                     BIGINT
      
  
SELECT @ProductVersionText = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20))
  
SELECT @ProductVersionInt = CAST(SUBSTRING(@ProductVersionText, 1, CHARINDEX('.', @ProductVersionText) -1)AS INT)

  
IF @ProductVersionInt <> 9  -- other than 2005
      
RETURN -- no data returned
      
  
ELSE IF @ProductVersionInt = 9  -- 2005
      
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info


  
INSERT @ProcessorUsageforLast4Hours2005
      
(
          
Record_ID,
          
EventTime,
          
SQLProcessUtilization,
          
SystemIdle,
          
OtherProcessUtilization
      
)  
  
SELECT
      
Record_ID                                               AS Record_ID,
      
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE())  AS EventTime,
      
SQLProcessUtilization                                   AS SQLProcessUtilization,
      
SystemIdle                                              AS 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                                                                                   AS 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
      
   RETURN
END


/*

SELECT * FROM dbo.udf_GetProcessorUsageforLast4Hours2005()

*/





SQL Server 2008 onward:


DROP FUNCTION dbo.udf_GetProcessorUsageforLast4Hours2008
GO

CREATE FUNCTION dbo.udf_GetProcessorUsageforLast4Hours2008 ()
RETURNS @ProcessorUsageforLast4Hours2008 TABLE
  
(
      
Record_ID                   INT,
      
EventTime                   DATETIME,
      
SQLProcessUtilization       DECIMAL(18,2),
      
SystemIdle                  DECIMAL(18,2),
      
OtherProcessUtilization     DECIMAL(18,2)
   )
AS
/***************************************************************
Purpose:   To retrieve details of processor usage from the last 4 hours.
           Uses the SystemHealth exteneded event ring buffer
           Valid for SQL 2008 and above as the structure of the sys.dm_os_sys_info
           view has changed from SQL 2005

           The following DMVs are used:
               sys.dm_os_sys_info      - contains OS version information
               sys.dm_os_ring_buffers  - contains a record of all currently active ring bufers and their contents        
          
Author:        Unknown
History:   25 Aug 2009

****************************************************************/

BEGIN

   DECLARE
      
@ProductVersionText         VARCHAR(20),
      
@ProductVersionInt          INT,
      
@ts_now                     BIGINT
      
  
SELECT @ProductVersionText = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20))
  
SELECT @ProductVersionInt = CAST(SUBSTRING(@ProductVersionText, 1, CHARINDEX('.', @ProductVersionText) -1)AS INT)

  
IF @ProductVersionInt <= 9  -- 2005 or earlier
      
RETURN -- no data returned
      
  
ELSE IF @ProductVersionInt > 9  -- 2005
      
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info;


  
INSERT @ProcessorUsageforLast4Hours2008
      
(
          
Record_ID,
          
EventTime,
          
SQLProcessUtilization,
          
SystemIdle,
          
OtherProcessUtilization
      
)  
  
SELECT
      
Record_ID                                               AS Record_ID,
      
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE())  AS EventTime,
      
SQLProcessUtilization                                   AS SQLProcessUtilization,
      
SystemIdle                                              AS 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                                                                                   AS 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
      
   RETURN
END


/*

SELECT * FROM dbo.udf_GetProcessorUsageforLast4Hours2008()

*/


ċ
udf_GetProcessorUsageforLast4Hours2005.sql
(3k)
Andy Hughes,
Aug 23, 2012, 9:57 AM
ċ
udf_GetProcessorUsageforLast4Hours2008.sql
(3k)
Andy Hughes,
Aug 23, 2012, 9:57 AM
Comments