Return top 20 queries by Average CPU

DatabaseExpertise.com

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

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

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

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

Please register or log in to leave a comment.