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:
- Identify the query that is causing high CPU
- Identify the total time taken by query to execute that query
- Find out when was the last time the query was executed
- 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:
--Author: Saleem Hakani (http://sqlcommunity.com)
--Query to find top 50 high CPU queries and it's details
SELECT TOP 50
Convert(varchar, qs.creation_time, 109) as Plan_Compiled_On,
qs.execution_count as 'Total Executions',
qs.total_worker_time as 'Overall CPU Time Since Compiled',
Convert(Varchar, qs.last_execution_time, 109) as 'Last Execution Date/Time',
cast(qs.last_worker_time as varchar) +' ('+ cast(qs.max_worker_time as Varchar)+' Highest ever)' as 'CPU Time for Last Execution (Milliseconds)',
Convert(varchar,(qs.last_worker_time/(1000))/(60*60)) + ' Hrs (i.e. ' + convert(varchar,(qs.last_worker_time/(1000))/60) + ' Mins & ' + convert(varchar,(qs.last_worker_time/(1000))%60) + ' Seconds)' as 'Last Execution Duration',
qs.last_rows as 'Rows returned',
qs.total_logical_reads/128 as 'Overall Logical Reads (MB)',
qs.max_logical_reads/128 'Highest Logical Reads (MB)',
qs.last_logical_reads/128 'Logical Reads from Last Execution (MB)',
qs.total_physical_reads/128 'Total Physical Reads Since Compiled (MB)',
qs.last_dop as 'Last DOP used',
qs.last_physical_reads/128 'Physical Reads from Last Execution (MB)',
t. 'Query Text',
qp.query_plan as 'Query Execution Plan',
DB_Name(t.dbid) as 'Database Name',
t.objectid as 'Object ID',
t.encrypted as 'Is Query Encrypted'
--qs.plan_handle --Uncomment this if you want query plan handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.last_worker_time DESC
Go
Let’s look at each of the columns and what their value means:
| Column Name | Value / Meaning |
| 1. Plan Compiled On | This is the date when the query plan was compiled |
| 2. Total Executions | This shows the number of times the query/sproc was executed since the plan was compiled |
| 3. Overall CPU Time Since Compiled | This is a total amount of CPU time taken for all the executions since the query was compiled |
| 4. Last Execution Date/Time | This 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 |
Recent Comments