Monday, August 23, 2010

DMVs for Query Plan Metadata

Excellent article around troubleshooting slow query performance.

This query also allows you to see historical queries executed.

SELECT TOP 100
        max(last_execution_time) LastExecutionTime,
        SUM(total_logical_reads) AS total_logical_reads ,
        COUNT(*) AS num_queries , --number of individual queries in batch
        --not all usages need be equivalent, in the case of looping
        --or branching code
        MAX(execution_count) AS execution_count ,
        MAX(execText.text) AS queryText
FROM    sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS execText
GROUP BY deqs.sql_handle
ORDER BY 1 DESC

DMVs for Query Plan Metadata