Does Query Store force plan persist following server restart?

Question

Would like to ask because cannot find this information.

Does Query Store force plan persist following server restart?

In which cases will forcing a plan via query store not remain in effect?

Answer 1

Yes, forced plans work across server restarts. This is because the information about plan forcing is stored in the database and persisted on disk with other user data. You can see this in the Query Store DMVs:

SELECT * 
FROM sys.query_store_plan
WHERE is_forced_plan = 1;

If you think a plan should be forced but it isn't, you can set up an Extended Event session for query_store_plan_forcing_failed to try and get more information about why it's failing.

In which case forcing plan via query store will not take effect?

The list of reasons plan forcing might not take effect are here under "last_force_failure_reason_desc": sys.query_store_plan (Transact-SQL)

Some other more mundane reasons might be that

• the forced plan was purged from the Query Store data (because of automated cleanup, or someone manually purging the query store), or
• the query is running on a readable secondary (where plan forcing does not apply), or
• the query text changed in a way that caused it to get a new query hash, and thus no longer match for the purposes of plan forcing