on February 28, 2018
Nope.
At least, not right now.
I started thinking about this when I noticed that the sys.sp_query_store_unforce_plan requires you to specify both a @query_id and a @plan_id.
If there’s only ever one plan that can be forced for a query, why would I need to specify the @plan_id?
I’ve got no insider knowledge on this, I just started thinking about it.
Current behavior: SQL Server 2017 allows you to run sys.sp_query_store_force_plan multiple times for the same @query_id, but…
It will only force the plan for the most recent statement you ran.
If I have a query with id=4 which has two plans with plan ids 3, and 5, and I run this TSQL all at once:
EXEC sys.sp_query_store_force_plan @query_id = 4, @plan_id= 3;
GO
EXEC sys.sp_query_store_force_plan @query_id = 4, @plan_id= 5;
GO
For a brief moment after the first statement runs, @plan_id 3 will be forced.
After both statements complete, only @plan_id 5 will be forced.
There can only be one!
Why would it be cool to be able to force more than one plan?
We might have a parameterized query that we want multiple plans for, depending on how it’s executed.
The query is executed with @parameter_x = null, I want Plan A.
Otherwise I want Plan B.
Could be nifty!
It is possible that @plan_id is required to prevent accidents
Perhaps it’s required to specify the @plan_id in case I make a mistake, and specify the wrong query id for a plan id, and it wants to make sure I really have it right?
It’s possible, but doesn’t seem super likely to me. I’d rather optimistically hope that some day plan forcing will become even more flexible.
Will it ever happen? I have no idea :smile: