A plan force can rescue a query in one click and fail after the next index change. Forcing a plan in Query Store needs a second step: checking that SQL Server can still apply it and that the workload remains faster. The checkbox alone does not prove either result.
Confirm the Query and Candidate Plan
Query Store keeps query IDs and plan IDs inside each database. Find the affected query by text, then compare its plans and runtime stats before forcing one. The fastest plan for one parameter can be poor for another. I inspect representative values and the current index set before making a force. A plan from last month can refer to an access path that no longer exists.
DECLARE @QueryID bigint = 0;
SELECT plan_id, query_id, is_forced_plan,
force_failure_count,
last_force_failure_reason_desc,
last_execution_time, query_plan
FROM sys.query_store_plan
WHERE query_id = @QueryID
ORDER BY last_execution_time DESC;Replace zero with the confirmed query ID. If the query has only one plan, there is no known alternative in this database's Query Store history. Check capture state and the workload before trying to force a plan from a different query. Plan IDs are not portable labels across databases.
Forcing a Plan for the Chosen Pair Only
After comparing runtime and plan shape, call sp_query_store_force_plan with the exact query ID and plan ID. The procedure returns a status, but that status only tells you the request was accepted. Future compilations still need to produce a plan that can honor the force. Run the affected query again under application-like settings and check the outcome. Left with its zero placeholders, the call below stops with error 12402, because query ID 0 does not exist.
DECLARE @QueryID bigint = 0;
DECLARE @PlanID bigint = 0;
EXEC sys.sp_query_store_force_plan
@query_id = @QueryID,
@plan_id = @PlanID;Replace both zeros. Do not force a plan simply because its average duration is lower over all history. Compare matching time windows and execution counts. If the good plan ran only once with a rare parameter, its average is a weak basis for a database-wide decision. What parameter values will the forced plan have to serve tomorrow?
Check Whether Forcing a Plan Held After Execution
sys.query_store_plan exposes is_forced_plan, force_failure_count, and last_force_failure_reason_desc. The forced flag means the plan is designated. A nonzero failure count or reason says the optimizer could not reproduce a suitable shape on at least one compile. Read these fields after the workload has executed, not just immediately after calling the procedure.
SELECT query_id, plan_id, is_forced_plan,
force_failure_count,
last_force_failure_reason_desc,
last_compile_start_time, last_execution_time
FROM sys.query_store_plan
WHERE query_id = 0;Replace zero with the target query ID. The failure count increments on failed recompilation attempts, not on every execution. A value of zero does not prove the query ran after the force. Confirm last execution and compare runtime stats. If the force failed, the query can still run using a newly compiled alternative plan. That is why the failure can be quiet from the application's point of view.
Understand Index and Schema Failures
A forced plan can depend on an index that was dropped, disabled, or changed. The optimizer cannot force an illegal access path. The failure reason can identify a missing index or other mismatch. Before recreating an index, ask whether the newer schema deliberately replaced it. A plan force should not hold a migration hostage because nobody reviewed the dependency.
I keep a change note naming indexes used by the forced plan. Before an index cleanup, query the force inventory and inspect those plans. If the old plan cannot be forced, compare the new best plan with the old one and update the tuning decision. Recreating an obsolete index solely to make a checkbox green can impose a large write cost.
Check Runtime, Not Just Metadata
Even when forcing succeeds, the plan can become less useful as data distribution changes. Compare CPU, duration, logical reads, and waits for the query before and after. Query Store intervals and execution counts keep the comparison grounded. If the force improves one value and hurts another, consider parameter-sensitive plan behavior, filtered statistics, or a query rewrite.
A forced plan can be the right temporary control while a root-cause fix is developed. Give it an owner and a review date. I would not leave a force in place indefinitely without knowing what it was protecting against. The server will happily keep following an old map after the traffic pattern changes.
Run a Weekly Force Inventory
This query lists every forced plan in the current database and its latest failure information. Schedule it as a read-only report each week, and alert when force_failure_count rises or a nonzero last_force_failure_reason appears. Keep Query Store actual state and retention in the report too. One database can have several forces and, on newer configurations, replica-specific behavior.
SELECT p.query_id, p.plan_id,
p.force_failure_count,
p.last_force_failure_reason_desc,
p.last_execution_time,
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.is_forced_plan = 1
ORDER BY p.force_failure_count DESC, p.last_execution_time DESC;Run it in each database with Query Store enabled. A plan that has not executed recently deserves review even with zero failures; the protected workload can have moved or disappeared. Save weekly results so a new failure is visible against the prior week. An empty result can mean there are no forces, or that you connected to the wrong database. Include DB_NAME() in the report header.
Stop Forcing a Plan When the Root Cause Is Fixed
After a statistics, index, or query change, test without the force in a safe window. Use sp_query_store_unforce_plan for the same query and plan IDs, then monitor representative executions. Keep the rollback option available until the new behavior is stable. A force that is no longer needed adds operational debt and can fail at the next schema change.
Before removing it, save a baseline from Query Store and agree on a short observation window. If the new natural plan regresses, restore the known good force while investigating. That makes unforcing a measured test rather than a leap of faith. Keep both the rollback action and the owner in the change note.
I close the work only when the query's runtime is acceptable and the weekly inventory reflects the intended state. Plan forcing is a useful control. Its value comes from verification, not from the ease of clicking a button in SSMS.
Related reading on this blog: Create Efficient Query Plans Using Query Store: Analyzing SQL Server Query Plans: Part 3 and Query Store Status for All the Databases.
A forced plan is not permanent insurance, it is a monitored choice with a removal path.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
