Get CPU Usage for Last 4 Hours

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

*/