http://www.sqlservercentral.com/blogs/databaseexpertisecom/2010/08/31/return-top-20-queries-by-average-cpu/

Printed 2014/11/22 07:23AM

Return top 20 queries by Average CPU

By Justin H-Davies, 2010/08/31

I’ve recently used this when troubleshooting some unexplained high CPU load on my server:

select top 20
st.objectid, st.dbid, 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

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.