November 9, 2009

sys.dm_exec_query_stats


The “sys.dm_exec_query_stats” DMV is useful for analyzing performance stats of cached query plans in order to identify long running queries and/or procedures that might be missing indexes.
I use the following script a lot, enjoy!
SELECT TOP 5 creation_time, last_execution_time, total_clr_time, total_clr_time/execution_count
AS [Avg CLR Time], last_clr_time, execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY total_clr_time/execution_count DESC;
GO