Finding the CPU percentage used by SQL Server is perhaps the first step in performance monitoring. Often, finding the CPU % as shown by task manager, from SQL Server can be tricky as SQL Server's sysprocesses and exec_requests gives only CPU ticks and not the exact CPU % used. Logging into each and every server manually and checking the CPU can be a pain if you are administering a large number of servers. So here is a solution.
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms)
Dateadd(ms, -1 * ( @ts_now - [timestamp] ), Getdate()) AS eventtime,
100 - systemidle - sqlprocessutilization AS
FROM (SELECT record.value('(./Record/@id)', 'int')
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', 'int') AS sqlprocessutilization,
FROM (SELECT timestamp,
CONVERT(XML, record) AS record
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%
ORDER BY record_id DESC
The script provided above provides the approximate CPU usage % as shown in task manager. The result is shown below
Column description is provided below:
SQLProcessutilization: Percentage used by SQL Server process ( sqlserver.exe)
OtherProcessutilization: Percentage used by other processes in the server
Eventtime: Time at which the CPU percentage was recorded.
systemidle: Perecentage of CPU left.
record_id: Just a incremental row id
The CPU percentage is obtained from sys.dm_os_ring_buffers DMV. sys.dm_os_ring_buffers records the CPU percentage
for every one minute and it maintains CPU % for the last 256 minutes. Like all DMVs if the server is restarted then
the DMVs lose the data.
Now for the credits :)
The Script provided above is obtained from the scripts deployed by Performance dashboard tool explained in the last post Performance Dashboard uses the sp [MS_PerfDashboard].[usp_Main_GetCPUHistory] for getting the reports on CPU. I just tweaked the sp to obtain the CPU % for 256 minutes instead of the last 15 minutes returned by Performance Dashboard. Thanks Performance Dashboard again :)