A forced plan can save runtime pain yet still spend too long compiling. Optimized plan forcing uses a replay script to shorten repeated compilation for eligible forced plans.
Understand the Replay Script
SQL Server 2022 can store an optimization replay script with an eligible Query Store plan. When that plan is forced, the replay script guides a later compilation toward the same plan without repeating every optimization step. This helps complex queries with costly compilation. It is not a promise that every plan receives a script or that every compile becomes faster. I check the plan's Query Store flags before attributing a CPU change to the feature. A query that is slow during execution needs a runtime investigation as well.
The feature applies to plan forcing through Query Store. A query hint and an indexed view solve different problems. Keep the experiment focused on the compile-heavy query and its plan.
Check the Optimized Plan Forcing Setting
Query sys.database_scoped_configurations for OPTIMIZED_PLAN_FORCING. It is enabled by default for new SQL Server 2022 databases, but upgraded databases and local changes deserve inspection. Record the database compatibility level and Query Store state too. I do not turn a setting on instance-wide based on one slow report. The database scope lets you test and reverse the decision within the relevant workload.
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = N'OPTIMIZED_PLAN_FORCING';
SELECT actual_state_desc, desired_state_desc
FROM sys.database_query_store_options;Find a Plan With a Script
sys.query_store_plan exposes has_compile_replay_script and forcing state. A value of one means a script is recorded. Check is_forced_plan, force_failure_count, and failure reason as well. A forced plan that fails to apply does not support a success claim. I link the plan to its query text and plan_id so the team can inspect the exact statement. Query Store capture and retention policy affect what remains visible. In my test database, a twelve-table join compiled without receiving a script, so this query returned no rows. An empty result is a normal answer, not a broken query.
SELECT p.plan_id, p.query_id, p.is_forced_plan,
p.has_compile_replay_script, p.force_failure_count,
p.last_force_failure_reason_desc, qt.query_sql_text
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE p.has_compile_replay_script = 1
ORDER BY p.query_id, p.plan_id;Compare Compilation Cost
sys.query_store_query includes count_compiles and average or last compile duration. Record values before and after a controlled workload interval. Force the same eligible plan under comparable inputs, then compare compile count, compile duration, CPU, and runtime metrics. The counters can aggregate several compiles, so capture a defined interval rather than reading one lifetime average. I also inspect plan forcing failures. A lower compile time is useful only if query execution stays correct and stable. In the query below, replace 39 with the query_id from the previous result.
Do not measure by clearing all caches on a shared server. That changes the workload and hurts other users. Use a restored copy for controlled recompilation tests, or observe natural recompiles in production with careful timestamps.
SELECT query_id, count_compiles, avg_compile_duration,
last_compile_duration, last_compile_start_time
FROM sys.query_store_query
WHERE query_id = 39;
When Optimized Plan Forcing Does Not Help
Not every query is eligible for an optimization replay script. Schema changes, invalid forced plans, Query Store cleanup, and plan forcing failures can change the situation. Inspect the flags after deployment or upgrade, not only when the first test succeeds. I review runtime plan quality alongside compile savings. Forcing a poor plan more efficiently is a precise way to be wrong faster.
Ask whether compilation actually dominates the request. If execution CPU and reads are much larger, an index or query change can matter more. The feature earns attention when repeated compilations are a measurable part of the cost.
Distinguish Compile Time From Plan Quality
Optimized plan forcing targets compilation overhead for an eligible forced plan. It does not make a bad plan execute well. Compare average compile duration and count_compiles with runtime CPU, reads, and duration for the same query_id. A query can spend less CPU compiling and still get slower because the forced plan no longer fits current data. I keep both charts in the review. The smaller compile line is a success only when user-facing performance remains acceptable.
I also check whether the replay script actually exists for the forced plan. A database setting being ON is not proof that every query uses replay. If has_compile_replay_script is zero, investigate eligibility or capture conditions before attributing a change to the feature. Query Store's forcing failure details deserve the same attention.
Plan for Schema and Statistics Changes
Schema changes, index changes, and statistics updates can alter whether a forced plan is still valid or desirable. Revisit the plan after deployments that affect the query. I record the original reason for forcing, the query text, the plan_id, and the conditions under which it should be unforced. A replay script tied to an old shape should not become a forgotten production rule. Test a fresh plan in a restored copy when data distribution shifts.
What if compilation was expensive only during an unusual burst? A permanent forced plan can cost more in runtime over the rest of the week. Use a measured representative interval, not one dramatic call, to decide. I prefer fixing needless dynamic SQL or parameterization in application code when that is available. Optimized plan forcing is a targeted engine feature, not a substitute for understanding why compilations recur.
Check whether the forced plan remains the plan used after a deployment. Query Store can record a forcing failure, or a schema change can invalidate the old shape. I pair the replay-script flag with forcing status and actual plan use. If the plan is no longer forced, a lower compile duration cannot be credited to the feature without more evidence.
Keep Optimized Plan Forcing Reversible
Document the query_id, plan_id, setting, measured interval, and rollback action. If you enable the setting, use ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON in the chosen database and verify the view afterward. If plan forcing causes regressions, unforce the plan through Query Store procedures after reviewing dependencies. I keep the rollback decision tied to actual compile and runtime evidence, not to a single graph.
The target is a stable query with less compilation overhead. Query Store provides both the mechanism and the evidence. Read both sides before declaring the tuning finished.
Related reading on this blog: Forcing a Plan in Query Store and Checking That It Held and Query Store Feature for Secondary Replicas.
A forced plan is not a free compile, it is a plan whose compilation path can be shortened.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
