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)', 'int') AS id ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int') AS SystemIdle ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', '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
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)