Force a good plan or attach a hint with Query Store

Command

USE [Musteri];
-- Force the good plan (query_id / plan_id from the regression query):
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;

-- Forced plans and failure reason:
SELECT p.query_id, p.plan_id, p.is_forced_plan, p.plan_forcing_type_desc,
       p.force_failure_count, p.last_force_failure_reason_desc
FROM   sys.query_store_plan p
WHERE  p.is_forced_plan = 1;

-- Undo:
EXEC sys.sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;

-- 2022+: attach / remove a hint without changing code:
EXEC sys.sp_query_store_set_hints @query_id = 42, @query_hints = N'OPTION (RECOMPILE)';
SELECT query_id, query_hint_text, last_query_hint_failure_reason_desc
FROM   sys.query_store_query_hints;
EXEC sys.sp_query_store_clear_hints @query_id = 42;

What it does

Forces a specific Query Store plan for the chosen query; the optimizer uses that plan on every compilation. On 2022 you can alternatively attach hints such as RECOMPILE, MAXDOP or OPTIMIZE FOR without changing application code.

When to use it

When a plan regression needs an urgent fix in production, until a permanent fix (index, query rewrite, statistics) is ready.

Caution

A forced plan can degrade as data grows and is not a permanent fix; review it regularly. If an index used by the plan is dropped/renamed, forcing fails (last_force_failure_reason_desc = NO_INDEX etc.) and the query compiles normally. After forcing, a "morally equivalent" plan may appear under a new plan_id; this is expected. Forcing works while Query Store is READ_ONLY, but verify after a restore or AG failover.