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.
The Setup
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);
The Reveal
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.
Hi Grant
If the compile process is bypassed does this reduce the CPU cost of Compile / Recompile events?
I don’t know all the internals, so what I’m about to say is partly a small amount of knowledge and partly conjecture…
Yes, it does reduce the compile/recompile cost somewhat since it won’t go through a full optimization. To what degree, I’m not sure. However, if you’re capturing compile/recompile counts, you’ll see that it does go into the compile process even if it short circuits part way through that process.
[…] Grant Fritchey wonders, if you force a plan using Query Store but the plan ages out of cache, do you… […]
I’ve run both queries on my test system. I only see first plan in Query Store. My first execution produced merge join plan. Running SP for runtime parameter mentor reuses compiled value of London. How could I put the second plan in a store?
I just put the second plan in query store. First, I cleared procedure cache. then I run mentor query first. It put mentor plan in Query Store.
To ob,
You won’t automatically get a second plan in Query Store any more than you will automatically get a second plan in the plan cache. There has to be an event that removes the plan from cache and causes a recompile using the alternate parameter values to arrive at another plan. That would put that new plan into the plan cache, and a second plan would then be available within the Query Store.
Hi Grant,
I’ve a doubt about query store.
When I forced the plan, if I clean the plan cache, the forced plan is cleaned too?
Another doubt when I disable the QS the plans forced is cleaned too?
If you flush the cache, the forced plan is removed from cache. Then, when the query in question is executed again, plan forcing ensures that the same plan is placed back into cache. If you completely disable Query Store, yes, plan forcing stops. You can stop the data collection part of the Query Store and leave plan forcing in place. There’s a lot of flexibility.