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()

*/