There are times when you would want to know what query in SQL Server may be causing CPU spikes. There are many reason why a query may hit the CPU and the best way to troubleshoot it to:

  1. Identify the query that is causing high CPU
  2. Identify the total time taken by query to execute that query
  3. Find out when was the last time the query was executed
  4. Obtain Query execution plan for the offending query

There are many scripts available on the internet and many-a-times that confuses the DBA/Developer since there’s not much information on what each column value contains.

Here’s a query that would provide you with the top 50 queries that are hitting the CPU:

Let’s look at each of the columns and what their value means:

 Column NameValue / Meaning
1. Plan Compiled OnThis is the date when the query plan was compiled
2. Total ExecutionsThis shows the number of times the query/sproc was executed since the plan was compiled
3. Overall CPU Time Since CompiledThis is a total amount of CPU time taken for all the executions since the query was compiled
4. Last Execution Date/TimeThis date and time shows the last time the query was executed
5. CPU Time for Last Execution (Milliseconds) This value is the time in milliseconds taken for the query to execute the last time. Note: This column also includes the highest time taken ever since the query plan was compiled)
6. Last Execution Duration This is the same value as the above except that this is broken in to hours, minutes and seconds since converting milliseconds to minutes could be time consuming.
7.  Rows returned Number of rows returned from last query execution
8. Overall Logical Reads (MB) This is an overall logical reads value in MB (This is calculated as # of pages/128)
9. Highest Logical Reads (MB) This is the highest logical read value in MB since the last time query was compiled (This is calculated as # of pages/128)
10. Logical Reads from Last Execution (MB) This is the logical read value in MB from the last query execution (This is calculated as # of pages/128)
11. Total Physical Reads Since Compiled (MB) This is the total number of Physical reads in MB since the query was last compiled. (This is calculated as # of pages/128)
12. Last DOP used This value shows the value of degree of parallelism used during the last query execution
13. Physical Reads from Last Execution (MB) Shows the number of MB physically read from the database file during the last execution
14. Query Text Actual text of the query from the cache
15. Query Execution Plan Actual execution plan of the query generated from cache
16. Database Name Name of the database where the query was run from
17. Object ID Object ID of the query being investigated
18. Is Query Encrypted This shows if the query has encrypted columns