How to Force a Query Execution Plan in SQL Server

by

When the optimizer consistently chooses a poor execution plan for a query, you can force SQL Server to use a specific better-performing plan from Query Store. This can provide immediate relief while you investigate and fix the root cause of the poor plan choice.

To force a plan, use the sp_query_store_force_plan stored procedure:

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

Replace 123 with your query_id and 456 with the plan_id of the plan you want to force. Once forced, SQL Server will use that plan for all future executions of the query, regardless of what the optimizer would normally choose.

Finding the Right Plan to Force

Before forcing a plan, you need to identify which plan performs best. You can use the following to compare performance across all plans for the query (replacing 123 with the ID of the query in question):

DECLARE @query_id INT = 123; -- Replace with your query_id

SELECT 
    p.plan_id,
    SUM(rs.count_executions) AS total_executions,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
    AVG(rs.avg_cpu_time) / 1000.0 AS avg_cpu_ms,
    AVG(rs.avg_logical_io_reads) AS avg_logical_reads,
    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
GROUP BY p.plan_id
ORDER BY avg_duration_ms;

Look for the plan with the best performance metrics (lowest duration, CPU time, and logical reads). Once you’ve identified it, note the plan_id to force it.

Finding the Query ID

The previous example uses the query_id of the query in question. If you don’t know the query_id, you can search for your query in Query Store:

SELECT 
    q.query_id,
    qt.query_sql_text,
    q.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%your_table_name%'
ORDER BY q.last_execution_time DESC;

Replace your_table_name with a keyword from your query to locate it. You may need to refine the filter to reduce the number of rows returned.

Verifying the Forced Plan

After forcing a plan, you can use the following query to verify it’s active:

SELECT 
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    p.force_failure_count,
    p.last_force_failure_reason_desc,
    qt.query_sql_text
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 q.query_id = 123
  AND p.is_forced_plan = 1;

When is_forced_plan is 1, the plan is successfully forced. If force_failure_count is greater than 0, check last_force_failure_reason_desc to understand why the forcing might be failing.

When Plan Forcing Fails

Sometimes SQL Server cannot use the forced plan due to schema changes, missing indexes, or incompatible query hints. When this happens, SQL Server falls back to normal optimization and logs the failure.

You can use the following query to check for force failures:

SELECT 
    p.plan_id,
    p.force_failure_count,
    p.last_force_failure_reason_desc
FROM sys.query_store_plan p
WHERE p.query_id = 123
  AND p.force_failure_count > 0;

As mentioned, common failure reasons can include schema changes (dropped indexes, altered tables) or the plan containing hints that conflict with the current database state.

Important Considerations

Forced plans are temporary fixes. While forcing a plan can immediately resolve performance issues, it prevents the optimizer from adapting to changing data patterns or taking advantage of improvements in newer SQL Server versions. Always investigate why the optimizer chose the poor plan and address the root cause.

Common root causes include:

  • Outdated or missing statistics
  • Parameter sniffing issues
  • Missing indexes
  • Data distribution changes

Once you’ve fixed the underlying problem, you should unforce the plan to allow the optimizer to work normally again.