{"id":4000,"date":"2023-09-01T07:00:00","date_gmt":"2023-09-01T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=4000"},"modified":"2024-01-28T21:29:34","modified_gmt":"2024-01-28T18:29:34","slug":"pos-mporoyme-na-doyme-ti-cpu-usage-eichame-ana-l","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-doyme-ti-cpu-usage-eichame-ana-l\/","title":{"rendered":"How can we see what CPU Usage we had per minute in SQL Server"},"content":{"rendered":"

SQL Server upon installation creates a lightweight Extended Event<\/a> with a size of up to 5mb in which it records performance information. This Extended Event is called system_health<\/a> 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. <\/p>\n\n\n\n

But we must not forget that the information is made rollover <\/em><\/strong>and overlap with new data. So the amount of time we can see in the past is not very long.<\/p>\n\n\n\n

The code<\/h2>\n\n\n\n

All we need to do is to run the following code in a query window:<\/p>\n\n\n\n

SELECT time,100-record.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/SystemIdle)[1]', 'INT') as 'Cpu Usage %'\n          FROM ( \n                SELECT CONVERT(xml, record) AS [record],dateadd (ms,timestamp - (select ms_ticks from sys.dm_os_sys_info),getdate()) as 'Time'\n\t\t\t\tFROM sys.dm_os_ring_buffers\n                WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'\n                AND record LIKE '%<SystemHealth>%'\n\t\t\t    order by time desc) AS x <\/pre>\n\n\n\n
The result that will return to us is the CPU Usage we had per minute:<\/p>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n