We've all encountered this scenario - a normally fast-running query suddenly starts performing poorly because SQL Server cached a suboptimal execution plan. In my production environment, I have a parameterized query that executes every 5 minutes. 99% of the time it runs in milliseconds, but occasionally it gets stuck with a plan that takes 30+ seconds to execute.

First, we need to find the plan handle for the problematic query:

SELECT qs.plan_handle, qs.query_hash, qt.text, qs.execution_count,
       qs.total_logical_reads/qs.execution_count AS avg_logical_reads,
       qs.total_elapsed_time/qs.execution_count AS avg_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%YourQueryText%' 
ORDER BY avg_elapsed_time DESC;

Once you've identified the bad plan, here are three precise ways to remove it:

Method 1: Using DBCC FREEPROCCACHE

-- Remove specific plan using plan handle
DBCC FREEPROCCACHE(0x060006001ECA270EC0211D05000000000000000000000000);
-- Replace with your actual plan handle

Method 2: Using Plan Guide

-- Create plan guide to force parameterization
EXEC sp_create_plan_guide 
    @name = N'Fix_Bad_Plan',
    @stmt = N'SELECT * FROM Orders WHERE CustomerID = @CustomerID',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@CustomerID int',
    @hints = N'OPTION (OPTIMIZE FOR (@CustomerID = 42))';

Method 3: Query Store (SQL Server 2016+)

For newer versions, Query Store provides better control:

-- Force a specific plan
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;

To stop this from happening again:

• Use OPTION (RECOMPILE) for queries with highly variable parameters
• Implement proper parameterization to avoid parameter sniffing issues
• Consider using OPTIMIZE FOR hints for known-good parameter values

Set up regular monitoring to catch plan regressions early:

SELECT TOP 20
    qs.query_hash,
    qt.text,
    qs.execution_count,
    qs.total_elapsed_time/qs.execution_count AS avg_elapsed_time,
    qs.last_elapsed_time/1000 AS last_elapsed_time_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.last_elapsed_time DESC;

In SQL Server 2008, we often encounter situations where a normally fast-running query (executing in milliseconds) suddenly starts performing poorly due to a cached bad execution plan. This typically happens when:

• The query uses parameters with skewed data distributions
• Statistics change after the plan is cached
• Initial parameter values create a suboptimal plan

Here are precise methods to evict just the problematic plan without clearing the entire cache:

-- Method 1: Using plan_handle from sys.dm_exec_cached_plans
DECLARE @plan_handle varbinary(64)
SELECT @plan_handle = plan_handle 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE text LIKE '%YourProblemQuery%' 
AND text NOT LIKE '%sys.dm_exec_cached_plans%'

IF @plan_handle IS NOT NULL
BEGIN
    DBCC FREEPROCCACHE(@plan_handle)
    PRINT 'Problematic plan removed successfully'
END

For more precise targeting when dealing with similar but distinct queries:

-- Method 2: Using query_hash
DECLARE @query_hash binary(8)
SELECT @query_hash = query_hash 
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
WHERE text LIKE '%YourProblemQuery%'

IF @query_hash IS NOT NULL
BEGIN
    DBCC FREEPROCCACHE(0x) + CONVERT(varchar(64), @query_hash, 2)
    PRINT 'Plans with matching query hash cleared'
END

To avoid recurrence after removing the bad plan:

-- Option A: Use OPTIMIZE FOR hint
EXEC sp_executesql N'SELECT * FROM Orders 
WHERE OrderDate > @date 
OPTION (OPTIMIZE FOR (@date = ''20200101''))',
N'@date datetime', @date = '20230101'

-- Option B: Force recompile each execution
EXEC sp_executesql N'SELECT * FROM Orders 
WHERE OrderDate > @date 
OPTION (RECOMPILE)',
N'@date datetime', @date = '20230101'

-- Option C: Use plan guide
EXEC sp_create_plan_guide 
@name = N'ForceGoodPlan',
@stmt = N'SELECT * FROM Orders WHERE OrderDate > @date',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@date datetime',
@hints = N'OPTION (OPTIMIZE FOR (@date = ''20200101''))'

After plan removal, verify with:

-- Check if plan still exists
SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE '%YourProblemQuery%'