The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

50 top high CPU queries

select top 50

st.objectid, st.dbid,

— object_name(st.objectid, st.dbid), — uncomment this line if you’re using SQL 2005 SP2

total_worker_time/execution_count AS AverageCPUTime,

CASE statement_end_offset

WHEN -1 THEN st.text

ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2)

END AS StatementText

from

sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY AverageCPUTime DESC

Advertisements

June 22, 2009 Posted by | Scripts, SQL SERVER | , , , , , , , | Leave a comment