Query Store, Plan Forcing, and DROP/CREATE

I absolutely love Query Store and the ability it provides to force a plan is amazing. However, there are a lot of little gotchas in this functionality. I just recently found one that has quite a far reaching effect. Let’s talk about what happens when you DROP and then CREATE a stored procedure.

Query Store and Plan Forcing

Let’s quickly recap how Query Store works and how Plan Forcing works. First, Query Store is driven by query, not by procedure, not by batch, but by query. Second, plan forcing is also by query. However, there are queries and there are queries. Let’s take this as an example:

CREATE PROC dbo.AddressByCity @City NVARCHAR(30)
AS
BEGIN
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;

SELECT *
FROM Production.Product AS p

END

Yeah, yeah, I know, no SELECT * queries as example code. But I’m doing this as a negative example, so it’s OK.

If I pass ‘London’ as a value to this procedure, the query against the Address table will get a hash match join along with some other stuff. If I pass ‘Mentor’ as a value, it gets a loop join. It’s bad parameter sniffing at work. So, we decide to force the plan in order to deal with bad parameter sniff.

All well and good. Then someone points out that we have a SELECT * query in our code and we need to do a new release.

Deploy The Fixed Procedure

We’re going to fix that code right now:

IF EXISTS
(
    SELECT o.object_id
    FROM sys.objects AS o
    WHERE o.name = 'AddressByCity'
)
BEGIN
    DROP PROCEDURE dbo.AddressByCity;
END;
GO
CREATE PROC dbo.AddressByCity @City NVARCHAR(30)
AS
BEGIN
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;

SELECT p.ProductID
FROM Production.Product AS p

END
GO

Done.

But, ready for the bad news?

Yeah, you’re seeing parameter sniffing all over again. Why? You fixed it right? You forced the plan to the one you wanted.

Ah, but then you dropped the procedure. Now, even though the Query Store is query driven, one part of the definition of the query is the object_id. When you use the code above, you get a new object_id value. That means you get a new query. Which means, no more plan forcing.

Fixing the Issue

The fix is actually really simple. Instead of, what is now, old-fashioned syntax, dropping and recreating the procedure, we’ll use the new CREATE OR ALTER command:

CREATE OR ALTER PROC dbo.AddressByCity @City NVARCHAR(30)
AS
BEGIN
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;

SELECT p.ProductID
FROM Production.Product AS p

END
GO

This ensures that the object_id will stay the same. Well, not the same as the one we originally forced. That’s actually still there in the Query Store. But, if we restarted this whole exercise and instead of DROP/CREATE, we used CREATE OR ALTER, then we would protect our plan forcing.

Check the plan after you use CREATE or ALTER and you’ll see that it is still being forced:

The property “Use plan” is only ever in the first operator of the plan when the plan is being forced.

Conclusion

As with anything new and cool, there are going to be some surprises. This is one of those surprises. If you start using Query Store, you either have to change how you deploy procedures going forward, or, you need to plan on setting up a new forcing scheme after you drop & recreate the procedure.

---

Every time I learn a little, I try to blog about it and share. However, if you want to get some intensive learning time, sitting in the room with me, asking questions, getting direct feedback is a great way to go about. I have a couple of opportunities for this kind of thing coming up.

I’ll be teaching Query Store, Extended Events and Execution Plans along with a few other things at SQLIntersection this spring:

SQLIntersection: April 7-9

On the other hand, if you want to talk DevOps, I have an all day class at Bits:

SQLBits: April 1