Indicate "morally equivalent" forced plan where use_plan = true but is_forced = 0
Currently, if you force a plan in query store (or if it is forced by automatic plan correction), the exact plan which is forced has isforced = 1 in sys.querystoreplan. In the plan properties, there is no useplan indication.
After this plan is forced, often a "morally equivalent" plan will be compiled, which has a different queryplanid and different costs, but the same plan shape.
This "morally equivalent plan" has isforced = 0 in sys.querystoreplan. In the execution plan properties, useplan = true.
These "morally equivalent plans" are currently difficult to identify in the Query Store reports and in the DMVs, because you really have to dig to find the use_plan = true property.
Ideas to improve the user experience:
1) Put some visual indication on a graphic plan on the top left most operator whenever use_plan = true (for whatever reason), to make the fact that something has been forced more obvious
2) Put a check mark (or some indication) on the bubbles in Query Store reports when use_plan = true
3) Make some way to identify these in sys.querystoreplan. (Is_forced = 2?)
Or just anything you can do to make these easier to understand for folks.
More details and a repro script are here: https://sqlworkbooks.com/2018/03/forced-plan-confusion-is_forced-vs-use-plan-true/
Thank you for making SQL Server more awesome!
Kendra Little commented
More info: I confirmed that the "morally equivalent plan" has a different query_plan_hash from the forced plan.
Here is a direct link to the demo code used in the blog post I linked: https://gist.github.com/LitKnd/494008b7073374a634791f072326ad05