How can we see what CPU Usage we had per minute in SQL Server
Latest posts by Stratos Matzouranis (see all)
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
SQL Server upon installation creates a lightweight Extended Event with a size of up to 5mb in which it records performance information. This Extended Event is called system_health and in this article we will see how we can with a simple query see what CPU Usage we had on the server per minute.
But we must not forget that the information is made rollover and overlap with new data. So the amount of time we can see in the past is not very long.
The code
All we need to do is to run the following code in a query window:
SELECT time,100-record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'INT') as 'Cpu Usage %' FROM ( SELECT CONVERT(xml, record) AS [record],dateadd (ms,timestamp - (select ms_ticks from sys.dm_os_sys_info),getdate()) as 'Time' FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%' order by time desc) AS x
The result that will return to us is the CPU Usage we had per minute: