query-plan

How to delete or invalidate a query plan

Reboot / Restart
When SQL Server is rebooted, or the service is stopped and restarted, any DMV data is lost.

Flushed Cache
The hard way, delete all query plans from cache:

-- Never execute this on your production server
DBCC FREEPROCCACHE

or you can target specific plans:
DBCC FREEPROCCACHE(plan_handle)

Memory pressures
Keeping a large number of cached plans can quickly fill the available memory, especially in systems that create lots of SQL queries that differ subtly (for example, having different environment settings), resulting in separate cached plans for each one.
SQL Server manages the size of the procedure cache by maintaining the age of the execution plans in the cache. If a stored procedure is not re-executed for a long time, the age field of the execution plan can come down to 0, and the plan can be removed from the cache when SQL Server observes a pressure on its memory.

Recompiling
Recompile all queries related to a specific table or view:

EXEC sp_recompile <tablename>

Delete all query plans associated with a stored procedure.

-- Manually mark a stored procedure for recompilation, so it will be recompiled next time it is executed.
EXEC sp_recompile <procname>

Or you can enforce a query recompilation each time, then add a query hint to the end of the query:

OPTION (RECOMPILE)

Add the WITH RECOMPILE option when a stored proc is created:

CREATE PROCEDURE <ProcedureName>
WITH RECOMPILE
AS
...

When statistics are updated
More details about when statistics change.

Simple parametrization
Setting configuration option optimize for ad hoc workloads to 1, will store only query_hash into the cache and and on second execution (if it happens), the plan itself will be placed in cache.

Forced parametrization
Parametrize all queries like in case of simple parametrization

ALTER DATABASE <dbname> SET PARAMETRIZATION FORCED;

List of cached query plans

dm_exec_cached_plans returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

SELECT
      CP.[objtype] -- ad hoc query, stored procedure, prepared statement, etc.
    , CP.[cacheobjtype]
    , CP.[size_in_bytes]
    , CP.[refcounts]
    , CP.[usecounts]
    , ST.[text]
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans as CP
    CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) as ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    1 = 1
    AND ST.[text] LIKE '%<string to find>%'
    --AND CP.[objtype] NOT IN (N'Adhoc', N'Prepared')
ORDER BY
    CP.[objtype]       

Performance statistics for cached query plans

SELECT
          DB_NAME(QP.dbid)
        , ST.[text]
        , QS.[execution_count]        
        , QS.query_hash
        , QS.query_plan_hash
        , QP.query_plan
        , QS.*
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
WHERE
        ST.[text] LIKE '%<part of your query here>%'
ORDER BY
        QS.[execution_count] DESC;
GO

Pages

Subscribe to RSS - query-plan