So, what do you do when you’ve been running a stress test, Sql CPU has been maxing out, and you’ve not been running profiler (to analyze in RML Utils) so have got no record of what happened – ans, provided te DMVs haven’t been cleared by the time you get to the box, the following will give you a fighting chance of finding the most cpu intensive statements, and maybe even their plans:
with cte as (SELECT top 100 percentquery_stats.plan_handle,SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS “Avg CPU Time”,MIN(query_stats.statement_text) AS “Statement Text”,MAX(query_stats.execution_count) as Execution_Count,SUM(query_stats.total_worker_time) as Total_Worker_TimeFROM(SELECT QS.*,SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,((CASE statement_end_offsetWHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END – QS.statement_start_offset)/2) + 1) AS statement_textFROM sys.dm_exec_query_stats AS QSCROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_statsGROUP BY query_stats.plan_handleORDER BY 2 DESC)SELECT cte.*, PL.query_planFROM cte CROSS APPLY sys.dm_exec_query_plan(cte.plan_handle) as PLORDER BY Total_Execution_Time desc, Execution_Count desc
Hopefully you’ll recognise it as a slightly re-hashed version of the BOL Sample. Thanks to Stuart Leeks for letting me use this in his Lab.
Note, you get a lot of data from this (our busy box returned 24,000 rows and 750Mb of data) so be aware that running this will record it’s own activity.
Original post by Ryan Simpson on Novemeber 12th 2010, here: http://rionisimpsoni.wordpress.com/2010/11/12/my-new-favourate-diagnostic-query-for-cpu/