3

Using SSMS 2016 Query Store I forced a few weeks ago a specific query to use a specific plan. Now every now and then I have the error:

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

I assume SET FORCEPLAN is passed automatically since the code executing that query doesn't. So I would like to review my forced plan, maybe cancel it. But I can't find that query in the query store anymore.

Hence my question: is there a way to list all forced query plans and then how can I cancel one?

2

You should be able to get information about 'forced' plans by querying this catalog view (sys.query_store_plan) filtering on is_forced_plan = 1. At the bottom of the page are links to other useful catalog views for Query Store.

This stored procedure (sp_query_store_unforce_plan) allows you to 'unforce' a plan.

  • Spot on, thanks! Actually I also just discovered by chance it's possible to search by query text using the magnifier in: Query Store -> Tracked Queries – Johann Sep 26 '16 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.