Forcing plans with Query Store can be a powerful tool— until it mysteriously fails. In real production systems, plan forcing sometimes just… doesn’t work. One common culprit is the cryptic OPTIMIZATION_REPLAY_FAILED error.

If you’re hitting OPTIMIZATION_REPLAY_FAILED, try re-forcing the plan using @disable_optimized_plan_forcing=1.

The Syntax You May Be Looking For

How to force a query plan without optimization replay:

EXECUTE sys.sp_query_store_force_plan
    @query_id = xxx,
    @plan_id = yyy,
    @disable_optimized_plan_forcing = 1;

Won’t it be great if Query Store on secondaries ever becomes a supported thing and we can use the @force_plan_scope parameter?

Quick Recap of Failed Forced Plans

Sometimes when you force a query plan, SQL Server has a problem enforcing the forcing. If plan forcing fails, the query still executes successfully:

“When a plan is forced for a particular query, every time SQL Server encounters the query, it tries to force the plan in the Query Optimizer. If plan forcing fails, an Extended Event is fired and the Query Optimizer is instructed to optimize in the normal way.” (docs for sp_query_store_force_plan)

When query plan forcing fails, the sys.query_store_plan view will contain some value for last_force_failure_reason_desc.

There is no benefit from leaving a query in a state where plan forcing is failing, so I’ve shared a script that finds and un-forces failed plans.

I’ve implemented versions of this that log the queries/plans found in a failed forced state along with the reason why plan forcing failed.

Many of those queries have OPTIMIZATION_REPLAY_FAILED as the reason.

Optimization Replay is Automatically on in SQL Server 2022+, Even If You’re Not at Database Compatibility Level 160

In my opinion, that error message should say OPTIMIZED_PLAN_FORCING_FAILED, because “optimization replay” is part of the feature named Optimized plan forcing:

Optimized plan forcing reduces compilation overhead for repeating forced queries… Once the query execution plan is generated, specific compilation steps are stored for reuse as an optimization replay script.

Optimized plan forcing (aka optimization replay) occurs in SQL Server 2022 and higher. It is enabled by default and works even if you are using a lower database compatibility level – for example, I regularly see this on SQL Server 2022 with db compat level 150.

Three Ways to Disable Optimized Replay

You can disable optimized plan forcing/ optimized replay in three different ways:

• At the query level, using the DISABLE_OPTIMIZED_PLAN_FORCING query hint
• At the database level, using a database scoped configuration (aka disable with ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF)
• When forcing a query plan by using the sys.sp_query_store_force_plan stored procedure with @disable_optimized_plan_forcing=1

I wish that when one forced a plan and there was an issue with the replay script, SQL Server would simply fall back to not using a replay script for plan forcing for that query. That doesn’t happen – you need to TRY to force a plan, then detect if plan forcing fails and for what reason. If you find it’s an optimization replay issue, you need to try to re-force the plan with a different parameter.

Or, if you don’t have time for all that, you can disable optimized plan forcing at the database level.

What If It’s GENERAL_FAILURE?

If query plan forcing is failing with the message GENERAL_FAILURE, check out my post on that topic.

TL;DR

If plan forcing fails with OPTIMIZATION_REPLAY_FAILED, re-force the plan using @disable_optimized_plan_forcing=1. It’s common in SQL Server 2022+, even at lower compatibility levels. Always check last_force_failure_reason_desc.