If you are using a query wrapped in IF EXISTS(...) and you find that you need to force a plan, you might notice the plan won’t force.  You’ll see this show up in sys.query_store_plan showing is_forced_plan = 1, but there will also be a force_failure_count value > 0 as well as a last_force_failure_description of GENERAL_FAILURE along with a last_force_failure_reason = 8695. GENERAL_FAILURE is a pretty generic error indicating that query store isn’t buying what you’re selling.

I put together a pretty simple example to help illustrate this, along with an alternative rewrite that doesn’t run into this.

The sample query:

This query is just doing some logic that checks if a record exists and returns a 1 if it does and a 0 if it doesn’t.
IF EXISTS(SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID)
SELECT 1
ELSE
SELECT 0


Reproducing the issue

Potential rewrite as a fix

SELECT TOP 1 @SalesOrderIDCount = COUNT(1) FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID
IF @SalesOrderIDCount = 0
SELECT 0
ELSE
SELECT 1

This accomplishes the same thing as the original query and has a similar plan along with runtime and CPU consumption. Disclaimer: this query is using the primary key of the table in the predicate (WHERE clause).  If the query were to check for the existence where multiple rows are involved the query would need to be modified to have a nested select to get the same performance.  I just did this for simplicity.

When I try forcing the plan associated with this query, free the procedure cache, and re-execute the query it no longer shows force plan failures:

Final thoughts

I wouldn’t go out of my way to code with the alternative fix approach; however, if you find you have a problematic query that has unpredictable plans and you need to force a consistent plan I’d consider this.  I’ve found plan guides have a similar problem, so keep in mind using plan guides seem to be affected by this as well.