Over a million developers have joined DZone.

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.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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)
SELECT a.AddressID,
 sp.Name AS StateProvinceName,
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),
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');


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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.


Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}