On occasion you might find the need to force a query plan. As time goes on you might see the plan you thought was forced revert to a different plan. This means you likely have a forced plan failure. In this post I’ll give you some tips on how to spot if you have forced plan failures.
Finding forced plan failures
This involves querying sys.query_store_plan for any plans where force_failure_count is greater than 0.
select p.plan_id, p.query_id, p.force_failure_count,
p.last_force_failure_reason, p.last_force_failure_reason_desc,
t.query_sql_text, convert(xml, p.query_plan) as plan_xml
from sys.query_store_plan p inner join sys.query_store_query q on p.query_id = q.query_id
inner join sys.query_store_query_text t on q.query_text_id = t.query_text_id
where p.force_failure_count > 0 and p.is_forced_plan = 1
If you have plans with forced failures your output would look like this:
The above query is looking for any forced plan failures. If you have a specific query that is not using a forced plan you can add the query_id into the predicate (where clause):
select p.plan_id, p.query_id, p.force_failure_count,
p.last_force_failure_reason, p.last_force_failure_reason_desc,
t.query_sql_text, convert(xml, p.query_plan) as plan_xml
from sys.query_store_plan p inner join sys.query_store_query q on p.query_id = q.query_id
inner join sys.query_store_query_text t on q.query_text_id = t.query_text_id
where p.force_failure_count > 0 and p.is_forced_plan = 1
and p.query_id = 4654791
Causes of forced plan failures
The last_force_failure_reason_desc gives you a description of the reason for the force failure. Most of the time (in my own experience) the reason falls under GENERAL_FAILURE which unfortunately is a generic bucket. I happened to write about one of these in a recent post.
3617: COMPILATION_ABORTED_BY_CLIENT
8637: ONLINE_INDEX_BUILD
8675: OPTIMIZATION_REPLAY_FAILED
8683: INVALID_STARJOIN
8684: TIME_OUT
8689: NO_DB
8690: HINT_CONFLICT
8691: SETOPT_CONFLICT
8694: DQ_NO_FORCING_SUPPORTED
8698: NO_PLAN
8712: NO_INDEX
8713: VIEW_COMPILE_FAILED
<other value>: GENERAL_FAILURE