As part of a licensing project, it was found to be very useful to get an overview of current servers CPU across the SQL estate. There was an expensive 3rd party software solution that was reporting high CPU usage on certain servers that had next to no usage when checking.
There is a wealth of free scripts as part of Glenn Berry Diagnostic scripts These are really really useful and worth a look if you do anything around SQL server. The one in question I utilised was query 44. The code below and can be found in the script link above
-- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 44) (CPU Utilization History) DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)); SELECT TOP(256) 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)', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', 'int') AS [SQLProcessUtilization], [timestamp] FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers WITH (NOLOCK) WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%%') AS x) AS y ORDER BY record_id DESC OPTION (RECOMPILE); ------
This will show the CPU usage for the last 256 minutes which in itself can be quite useful, if you come in one morning and server A job has run slowly, you can see if it was a CPU related issue quickly, however, this starts to be more useful if you capture this on a continual basis ( in theory every 256 mins) but I set to run hourly so would track server usage.
This was achieved simply by adding into a scheduled job. The whole job is here This creates a job and creates a table in master, that way you can across the SQL estate and collect the data locally and pull back to a central repository that allows the analysis of CPU .
The next blog post shows how to spoof up creating a result set of CPU results which uses some useful techniques in TSQL. I’ll also blog about pulling the data back to a central location