See Also: Main_Page - Transact SQL Code Library - Procedure Cache Related Queries

This query can almost replace a trace. It is actually better if you want to correlate queries with xml query plans. Adjust the sort as needed.

Use with caution if your server suffers from procedure cache bloat: if you have a large number of application databases on a server running a wide variety of queries, it may have a very large procedure cache. Generally, start by running this on a server with a very low amount of load to get a feel for the amount of resources required. It's not uncommon for this query to take several minutes on a server with a large number of ad-hoc queries in the procedure cache.

T-SQL Script

select total_worker_time/execution_count as AvgCPU  
, total_elapsed_time/execution_count as AvgDuration  
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads 
, execution_count   
, substring(st.text, (qs.statement_start_offset/2)+1  
, ((case qs.statement_end_offset  when -1 then datalength(st.text)  
else qs.statement_end_offset  
end - qs.statement_start_offset)/2) + 1) as txt  
, query_plan
from sys.dm_exec_query_stats as qs  
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st  
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp 
order by 1 desc

Query Test Checklist

• Works on SQL Server 2008: Yes
• Works on SQL Server 2005: Yes
• Works on SQL Server 2000: No
• Works on Standard Edition: Yes
• Works on case-sensitive servers: Yes

Tests Updated by Brent Ozar, 2009-04-01