For Day 16 of this series, we start out with Query #37, which is CPU Utilization History. This query retrieves information from the somewhat undocumented sys.dm_os_ring_buffers dynamic management view about recent CPU utilization by SQL Server. Query #37 is shown in Figure 1.
1: -- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 37) (CPU Utilization History)
2: -- This version works with SQL Server 2016
3: DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));
4:
5: SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
6: SystemIdle AS [System Idle Process],
7: 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
8: DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
9: FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
10: record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
11: AS [SystemIdle],
12: record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
13: AS [SQLProcessUtilization], [timestamp]
14: FROM (SELECT [timestamp], CONVERT(xml, record) AS [record]
15: FROM sys.dm_os_ring_buffers WITH (NOLOCK)
16: WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
17: AND record LIKE N'%<SystemHealth>%') AS x) AS y
18: ORDER BY record_id DESC OPTION (RECOMPILE);
19:
20: -- Look at the trend over the entire period
21: -- Also look at high sustained Other Process CPU Utilization values
Figure 1: Query #37 CPU Utilization History
This query shows you the average CPU utilization history by the current instance of SQL Server, plus the summed average CPU utilization by all other processes on your machine are captured in one minute increments for the past 256 minutes. This lets you go back in time and see what has been happening with processor utilization over that period. It is always nice to know whether an episode of high CPU utilization has been sustained or whether it has been going on for just a short period. It is also nice to understand how much CPU other processes are using on your machine.
Ideally, you have some sort of monitoring system in place to let you review your history and trends for more than four hours. Not all 3rd party monitoring systems are created equal. Some are much better than others. Personally, I really like SQLSentry PerformanceAdvisor.
Query #38 is Top Worker Time Queries. This query retrieves information from the sys.dm_exec_query_stats dynamic management view, the sys.dm_exec_sql_text dynamic management function, and the sys.dm_exec_query_plan dynamic management function about the highest cumulative worker time queries across your entire instance. Query #38 is shown in Figure 2.
1: -- Get top total worker time queries for entire instance (Query 38) (Top Worker Time Queries)
2: SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text][/text], 50) AS [Short Query Text],
3: qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],
4: qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
5: qs.max_worker_time AS [Max Worker Time],
6: qs.min_elapsed_time AS [Min Elapsed Time],
7: qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
8: qs.max_elapsed_time AS [Max Elapsed Time],
9: qs.min_logical_reads AS [Min Logical Reads],
10: qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
11: qs.max_logical_reads AS [Max Logical Reads],
12: qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]
13: -- ,t.[text][/text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
14: FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
15: CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
16: CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
17: ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
18:
19:
20: -- Helps you find the most expensive queries from a CPU perspective across the entire instance
21: -- Can also help track down parameter sniffing issues
Figure 2: Query #38 Top Worker Time Queries
This query is very useful when you see any signs of CPU pressure. It helps you understand which cached queries are using the most CPU resources across your entire instance. You might notice that one line of the query is commented out. I do this so that the query results will copy and paste into Excel. If you are not going to copy/paste the results, then you will usually get some more useful information by including the last two columns in the SELECT statement.
If you are seeing very wide variations in minimum, average, and maximum worker time for a cached query, this might be evidence that you have a parameter sniffing issue (where some input values for a query produce results that are not well-suited for the cached version of the query plan that is being used). The new Query Store feature in SQL Server 2016 is going to be very useful for detecting and correcting these types of issues.
5 thoughts on “SQL Server Diagnostic Information Queries Detailed, Day 16”
Another issue on the second query again, but easy to figure out missing column ‘text’.
Query #3 is not always correct … below is the result on my server + is greater than 100 so
100 – SystemIdle – SQLProcessUtilization AS [Other Process CPU Utilization] is negative.
SQL 2008R2 always report correct number.
Is this a bug in SQL 2016 and later? I don’t know if it occurs on 2012 and 2014
6
96
769218750
41093750
90662
-397312
100
The ring buffers DMV is undocumented and unsupported by Microsoft. It sometimes gives inaccurate results when you have high core count machines. I don’t think they are planning on fixing it.