Dec 4, 2009

How to find SQL queries with high CPU usage

Using this query in SQL Server 2005, you can see the top 5 queries ranked by CPU usage (http://msdn.microsoft.com/en-us/library/ms189741(SQL.90).aspx):


select DB_NAME(QP.dbid) as "Context_Database",top_5.*

from (SELECT TOP 5 query_stats.plan_handle AS "Query_Hash",



SUM(query_stats.total_worker_time)/SUM(query_stats.execution_count)AS "Avg CPU Time",
MIN(query_stats.statement_text)AS "Statement Text"


FROM(



SELECT QS.*,

SUBSTRING(ST.text, (QS.statement_start_offset/2)+ 1,



((CASE statement_end_offset



WHEN-1 THEN DATALENGTH(st.text)



ELSE QS.statement_end_offset



END - QS.statement_start_offset)/2) + 1)AS statement_text



FROM sys.dm_exec_query_stats AS QS



cross apply sys.dm_exec_sql_text(QS.sql_handle)as ST

) as query_stats




GROUP BY query_stats.plan_handle


ORDER BY 2 DESC) as top_5
cross apply sys.dm_exec_query_plan(top_5.Query_Hash) as QP
;

No comments:

Post a Comment