Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Return top 20 queries by Average CPU

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

Comments

Posted by ianstirk on 2 September 2010

Hi,

Nice query.

You can discover a lot more about DMVs in this forthcoming book www.manning.com/stirk. Chapter 1 can be downloaded for free and includes scripts for:

A simple monitor

Finding your slowest queries

Find your missing indexes

Identifying what SQL is running now

Quickly find a cached plan

Thanks

Ian  

Posted by Justin Hostettler-Davies on 2 September 2010

Thanks Ian, I'll download and check it out.

Posted by wolf_Ste on 28 September 2010

Thanks, but how can i do the same but in SQL 2000?

Leave a Comment

Please register or log in to leave a comment.