How to Unforce a Query Execution Plan in SQL Server

by

When you’ve forced an execution plan in SQL Server’s query store and later want to allow the optimizer to choose plans freely again, you’ll need to unforce the plan. You might want to do this because you’ve fixed the underlying issue or the forced plan is no longer optimal. Whatever the reason, the solution is straightforward and easy.

To unforce a plan, use the sp_query_store_unforce_plan stored procedure:

EXEC sp_query_store_unforce_plan 
    @query_id = 123, 
    @plan_id = 456;

Replace 123 with your query_id and 456 with the plan_id of the forced plan. Once unforced, SQL Server resumes normal query optimization for that query.

Finding Which Plans Are Forced

To see all currently forced plans in your database:

SELECT 
    q.query_id,
    p.plan_id,
    qt.query_sql_text,
    p.force_failure_count,
    p.last_force_failure_reason_desc
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1
ORDER BY q.query_id;

This shows all forced plans along with their query text, making it easy to identify which ones you might want to unforce.

Finding Forced Plans for a Specific Query

If you know the query you’re interested in:

DECLARE @query_id INT = 123;

SELECT 
    p.plan_id,
    p.is_forced_plan,
    p.force_failure_count,
    p.last_force_failure_reason_desc
FROM sys.query_store_plan p
WHERE p.query_id = @query_id
  AND p.is_forced_plan = 1;

This returns only the forced plan(s) for that specific query.

Verifying the Plan Was Unforced

After unforcing a plan, verify it’s no longer active:

SELECT 
    p.plan_id,
    p.is_forced_plan
FROM sys.query_store_plan p
WHERE p.query_id = 123
  AND p.plan_id = 456;

When is_forced_plan is 0, the plan has been successfully unforced and the optimizer will make its own decisions again.

When to Unforce Plans

You should unforce plans when:

  • You’ve fixed the root cause. If you’ve updated statistics, added missing indexes, or resolved parameter sniffing issues, the optimizer should now choose good plans on its own.
  • Data patterns have changed. The forced plan might have been optimal for your data six months ago, but if data volume or distribution has changed significantly, a different plan might now be better.
  • You’re upgrading SQL Server. Newer versions often include optimizer improvements. Forced plans prevent you from benefiting from these enhancements.
  • The forced plan is failing. If force_failure_count is increasing, the forced plan is no longer compatible with your schema and should be unforced.

Monitoring After Unforcing

After unforcing a plan, it’s a good idea to monitor query performance to ensure the optimizer makes good choices:

DECLARE @query_id INT = 123;

SELECT 
    p.plan_id,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
    SUM(rs.count_executions) AS executions,
    MAX(rs.last_execution_time) AS last_used
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.query_id = @query_id
  AND rs.last_execution_time >= DATEADD(hour, -1, GETUTCDATE())
GROUP BY p.plan_id
ORDER BY last_used DESC;

This shows which plans are being chosen and how they’re performing since you unforced the plan. If performance degrades, you may need to investigate further or temporarily force the plan again while you address the issue.

Feel free to change DATEADD(hour, -1, GETUTCDATE() to a more suitable timeframe if needed.