SQL SERVER – Query to Get CPU Usage History for SQL Server and Operating System

I have seen a number of customers manage their SQL Server environments in an adhoc manner. These accidental DBA’s need to know what happened in the system in a chronological order or even worst need to know what led to the lead up to a particular problem. Many times when we troubleshoot a problem with high CPU, its asked that when it all started and do we have any historical data of CPU usage?  Here is the one query which I have re-written to have more details.  Let us see Query to Get CPU Usage History for SQL Server and Operating System.

DECLARE @ticks_ms BIGINT
SELECT @ticks_ms = ms_ticks
FROM sys.dm_os_sys_info;
SELECT TOP 60 id
	,dateadd(ms, - 1 * (@ticks_ms - [timestamp]), GetDate()) AS EventTime
	,ProcessUtilization as 'SQL CPU'
	,SystemIdle 'Idle CPU'
	,100 - SystemIdle - ProcessUtilization AS 'Others (100-SQL-Idle)'
FROM (
	SELECT record.value('(./Record/@id)[1]', 'int') AS id
	,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
                          AS SystemIdle
	,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') 
                          AS ProcessUtilization
		,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 sub1
	) AS sub2
ORDER BY id DESC

My Test

I have run below query in SSMS from three connections.

SELECT *
  FROM [WideWorldImporters].[Purchasing].[PurchaseOrderLines] a 
  , [WideWorldImporters].[Purchasing].[PurchaseOrderLines]  b

And here is what I saw as CPU usage history.

This alone would not help in finding what caused high CPU, but at least we have one more data point to dig further. These are a great way to explore and know how SQL Server is performing in a deployed system. I would be curious to know what tools and processes do you have for troubleshooting. Please let me know via comments below.

Reference: Pinal Dave (https://blog.sqlauthority.com)