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