Persist CPU Utilization from RingBuffer

CPU utilization of SQL Server can be obtained from. sys.dm_os_ring_buffers.Ring buffer contains the CPU utilization by all other processes. It is captured in one-minute increments for the past 256 minutes. 

In order to check the CPU utilization of SQL Server prior to that, we have to persist the ring buffer details into a permanent table. Steps below:-

Step 1:  Create a table in the DBA utility database

 

CREATE TABLE [dbo].[CpuUtilization] (
	[SqlCpuUtilization] [int] NOT NULL
	,[SystemIdleProcess] [int] NOT NULL
	,[OtherProcessCpuUtilization] [int] NOT NULL
	,[EventTime] [datetime] NOT NULL
	,CONSTRAINT [PK_dbo_CpuUtilization_EventTime] PRIMARY KEY CLUSTERED ([EventTime] ASC)
	)

Step 2:  Create a job to run the below script to capture ring buffer data & write it into the table created in Step 1

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @T TABLE (
	[SqlCpuUtilization] [int] NOT NULL
	,[SystemIdleProcess] [int] NOT NULL
	,[OtherProcessCpuUtilization] [int] NOT NULL
	,[EventTime] [datetime] NOT NULL
	);
DECLARE @ts_now BIGINT = (
		SELECT cpu_ticks / (cpu_ticks / ms_ticks)
		FROM sys.dm_os_sys_info WITH (NOLOCK)
		);

INSERT INTO @T
-- This version works with SQL Server 2014
SELECT TOP (256) SQLProcessUtilization AS [SQL Server Process CPU Utilization]
	,SystemIdle AS [System Idle Process]
	,100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
	,DATEADD(ms, - 1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
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]
	FROM (
		SELECT [timestamp]
			,CONVERT(XML, record) AS [record]
		FROM sys.dm_os_ring_buffers WITH (NOLOCK)
		WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			AND record LIKE N'%<SystemHealth>%'
		) AS x
	) AS y
ORDER BY record_id DESC
OPTION (RECOMPILE);

INSERT INTO [dbo].[CpuUtilization]
SELECT T.*
FROM @T AS T
LEFT OUTER JOIN dbo.CpuUtilization AS C ON T.EventTime = C.EventTime
WHERE C.EventTime IS NULL
OPTION (RECOMPILE);
	--select * from [dbo].[CpuUtilization]

Step 3:  Clean up old data (if required)

SET NOCOUNT ON;

DECLARE @RetentionDay DATETIME;

SET @RetentionDay = (
		SELECT CAST(DATEADD(DAY, - 30, GETDATE()) AS DATE)
		);

DELETE
FROM dbo.CpuUtilization
WHERE EventTime < @RetentionDay;

Done!! I hope this helps 😊