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.
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.
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.