Removing Problem Query Plans from the Plan Cache

I often encounter queries that have fallen victim to parameter sniffing during my day to day activities. While the long term resolution for these types of problems often involves query modifications or the use of plan guides, a short term fix may involve forcing the recompilation of the query or procedure.

Stored Procedures and Triggers

Stored procedures and triggers may be marked for recompilation with ease. The system stored procedure sp_recompile may be used for this purpose.

EXECL sp_recompile N'<procedure name>';

SQL Server 2000

In the days of SQL Server 2000 our options were limited to the use of DBCC FREEPROCCACHE to remove everything in the plan cache or DBCC FLUSHPROCINDB(<db_id>) to remove the plans for a single database. These options are a little drastic to deal with a single query plan as they clear all objects from the plan cache or all objects associated with a single database respectively. These commands must be used with caution as they will have a temporary negative impact on performance while the plans are recompiled.

SQL Server 2005

Starting with SQL Server 2005, it became possible to remove a single plan using the Plan Guide feature. This process that I learned from Kalen Delaney involves creating a plan guide with the OPTION (RECOMPILE) hint for the problem query and removing the plan guide once it has removed the problem query.

EXEC sp_create_plan_guide 
    @name = N'RemovePlan',
    -- Your problem SQL statement will need to be assigned to the @stmt parameter.
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@Country',
    @hints = N'OPTION (RECOMPILE)';

The plan will be removed from the cache as soon as the plan guide is created. This can verified by inspecting sys.syscacheobjects. The query will continue to be recompiled if the plan guide is left in place, so the next step would be to remove the plan guide.

EXEC sp_control_plan_guide N'drop', N'RemovePlan';

SQL Server 2008 and Later

The 2008 and later versions of DBCC FREEPROCCACHE gained the ability to remove elements from the plan cache by specifying a plan handle, SQL handle or resource pool. The plan handle may be obtained from one of the following dynamic management objects:

The SQL handle may be obtained from one of the following dynamic management objects:

Shown below is one my favorite queries for finding problem query plan candidates. It will give you the top five query plans ordered by total CPU time.

WITH top_queries AS
  (SELECT TOP 5 
       plan_handle,
       SUM(total_worker_time) / SUM(execution_count) AS avg_cpu_time,
       SUM(total_worker_time) AS total_cpu_time,
       SUM(execution_count) AS total_execution_count,
       COUNT(*) AS number_of_statements
   FROM sys.dm_exec_query_stats
   GROUP BY plan_handle
   ORDER BY total_cpu_time DESC
 )
SELECT
    plan_handle,
    avg_cpu_time,
    total_cpu_time,
    number_of_statements,
    total_execution_count,
    DB_NAME(st.dbid) AS database_name,
    OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,
    OBJECT_NAME(st.objectid, st.dbid) AS object_name,
    qp.query_plan,
    st.text AS batch_text
FROM top_queries
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp;

Once you have the plan handle for the problem query you can remove it from the cache with DBCC FREEPROCCACHE.

-- The plan handle below will need to be replaced with the plan handle for 
-- your problem query.
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);