Get CPU Utilization History for last 60 minutes in SQL Server


Through below query you are able to find out the CPU Utilization history in SQL Server with in one minute intervals.



DECLARE
 @ts_now BIGINT = ( SELECT cpu_ticks / ( cpu_ticks / ms_ticks )
 FROM sys.dm_os_sys_info) ;
 SELECT TOP ( 60 )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
 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'AND record LIKE N'%<SystemHealth>%') AS x) AS y 
 ORDER BY record_id DESC ;

 

you will get the output like below:-



 

Leave a Reply

Your email address will not be published. Required fields are marked *