Forcing Query Store Plans

Grant Fritchey wonders, if you force a plan using Query Store but the plan ages out of cache, do you still use the forced plan?

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:

1 2 3 4 5 6 7 8 9 10 11 12

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:

It’s a good question with a good answer.