Plans getting recompiled most often

Which query plans are getting recompiled most often

Last updated:

Warning: Review and test in a non-production environment before running.

Back to results

-- Which query plans are getting recompiled most often
SELECT TOP 10
    plan_generation_num, execution_count,
    (SELECT DB_NAME([dbid]) + ISNULL('..' + OBJECT_NAME(objectid), '') FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset
        ) / 2)
        FROM sys.dm_exec_sql_text([sql_handle]) AS est) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND plan_generation_num > 1
ORDER BY plan_generation_num DESC
GO