Query Store, Forced Plans, and New Plans
Microsoft's Query Store introduces some new features when it comes to execution plans. Check out an answer to a question about how to force the right plan when you want to force it.
Join the DZone community and get the full member experience.Join For Free
i love questions. i recently received one about new plans in the query store (available in azure sql database now and in sql server 2016 after june 1).
let’s say you have selected a plan that you want to force. you set it up. now, let’s say the plan ages out of cache or even goes through a recompile. during the recompile, due to out of date statistics or skew in the statistics, you would, under normal circumstances, get a new plan. however, with query store and plan forcing, the plan that’s going to be used is the plan that is being forced. but, does that other plan, the one not used, get stored in query store?
i have no idea. let’s find out.
to start with, a small stored procedure that i use all the time for bad parameter sniffing demos that reliably gets different plans with different values due to statistics skew:
create proc dbo.spaddressbycity @city nvarchar(30) as select a.addressid, a.addressline1, a.addressline2, a.city, sp.name as stateprovincename, a.postalcode from person.address as a join person.stateprovince as sp on a.stateprovinceid = sp.stateprovinceid where a.city = @city;
if this procedure is called for a value of ‘london’ it gets a plan with a merge join. for most other value it gets a plan with a loops join. here’s an example of the ‘london’ plan:
i’ve run both queries on my test system. i can check their existence in query store this way:
select cast(qsp.query_plan as xml), qsq.query_id, qsp.plan_id, qsp.is_forced_plan from sys.query_store_query as qsq join sys.query_store_plan as qsp on qsp.query_id = qsq.query_id where qsq.object_id = object_id('dbo.spaddressbycity');
that’s going to return two (2) rows:
now, i’m going to remove the second plan, the ‘mentor’ plan, and i’m going to force the ‘london’ plan with two statements:
exec sys.sp_query_store_remove_plan @plan_id =219; exec sys.sp_query_store_force_plan 2,2;
now, when i execute the query, no matter what values i pass, i’m going to arrive at the ‘london’ plan. however, let’s pull the plan from cache in order to force a recompile:
declare @planhandle varbinary(64); select @planhandle = deps.plan_handle from sys.dm_exec_procedure_stats as deps where deps.object_id = object_id('dbo.spaddressbycity'); dbcc freeproccache(@planhandle);
when i execute the query using the value of ‘mentor’ do i get that plan stored in the query store? i know which plan will be in the cache. the ‘london’ plan because of i have chosen to force that plan. but, when i query the query store again to see what’s there for my stored procedure:
the short answer is nope. any possible new plans that could have been stored in query store are not there. yes, we have gone into the compile process, but, due to plan forcing, that process is getting a small bypass and the plan is simply applied from the query store. this, even though you’re going to see a compile or recompile event if you capture them using extended events. for more fun on recompiles and the query store, see this earlier post of mine.
keep those questions coming.
i love talking about query tuning. if you want to spend the day with me talking query tuning, you sure can. in august, i’ll be doing an all day pre-con at sqlserver geeks annual summit in bangalore india.
i’ll be doing a pre-conference seminar in oslo norway before sql saturday oslo in september.
Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.