Greetings! Today I was playing with query store and noticed that I had some failing forced plans. How do you find failing forced plans? I asked this question over and over and finally found an answer.
I started on my main database and ran this query to look at query store:
SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1 and force_failure_count > 0
I had over 20 of them that were failing! Next, I had to figure out how to unforce the failing plans. Some of them were so old, they wouldn’t come up when I tried to look for them using the plan id in the GUI. I did more digging and found this:
EXEC sp_query_store_unforce_plan @query_id = Enter your queryid, @plan_id = Enter your planid
It was incredibly satisfying to watch each of the rows in the first query disappear as I ran them through the second query. This is now on my list of things to check so I can have a clean and healthy query store.
This post’s song is High Low by The Unlikely Candidates