November 3, 2016 at 11:55 am
If I force a plan on a query that uses 'myindex' but then I delete 'myindex', the query still works, running as best it can. Is there an extended event or any other way to detect if & why a force plan has not actually been used?
November 3, 2016 at 12:56 pm
I assume you mean Query Store (as query hints forcing an index cause the query to fail if the index isn't there)
There's a couple of columns in sys.query_store_plan that let you track the failure to force a plan:
force_failure_count
Number of times that forcing this plan has failed. It can be incremented only when the query is recompiled (not on every execution). It is reset to 0 every time is_plan_forced is changed from FALSE to TRUE.
and
last_force_failure_reason_desc
Textual description of last_force_failure_reason_desc.
See https://msdn.microsoft.com/en-us/library/dn818155.aspx for the possible values for the latter.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply