Execution Plan of Cached Queries

----Get execution plan of cached queries orderd by Total Elapsed Time Desc

SELECT

total_logical_reads,

total_logical_reads / execution_count avg_reads,

total_logical_writes,

total_logical_writes / execution_count avg_writes,

total_physical_reads,

last_physical_reads,

min_physical_reads,

max_physical_reads,

last_logical_reads,

min_logical_reads,

max_logical_reads,

last_logical_writes,

min_logical_writes,

max_logical_writes,

creation_time,

last_execution_time,

total_elapsed_time,

convert(float, total_elapsed_time) / 1000000 'total_eplapsed_time(s)',

execution_count,

(convert (float, total_elapsed_time / execution_count) / 1000000) 'avg_duration(s)',

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 SQLTEXT,

qp.query_plan

FROM

sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp

where st.text like '%'

ORDER BY total_elapsed_time desc;