Forcing a Plan That Has a Plan Guide

The question that came up during a recent class I was teaching was: What if you have a plan guide to get the plan you want, but then decide, instead of using the plan guide, you’ll just force the plan?

Ummmm….

No idea. Let’s test it.

First, Create a Plan Guide

I have a couple of queries I use to teach about how statistics affects plan choice, so we’ll use that here. I’m going to also define and create a plan guide that makes this plan use a small row count for all queries against it:

CREATE OR ALTER PROC dbo.AddressByCity @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;
GO    

EXEC sys.sp_create_plan_guide @name = 'SniffFix',     
                              @stmt = N'    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;',
                              @type = N'Object',
                              @module_or_batch = N'dbo.AddressByCity',
                              @params = NULL,
                              @hints = N'OPTION(OPTIMIZE FOR(@City = ''Mentor''))'; 
GO

This is a really straight forward example of a plan guide. The only thing of note is that you should see that I have formatted my @stmt value exactly the same as what is in the definition of the procedure. This is necessary to get plan guides to work. If I now execute the query:

EXEC dbo.AddressByCity @City = N'London';

This results in the following execution plan:

If we look to the Properties of the SELECT operator, we can see the plan guide in use:

I also included the Parameter List so you can see how the plan guide affected the plan. The compile time value was for ‘Mentor’ even though the first execution of the procedure after being created was using the value of ‘London’. Otherwise, you can see that the PlanGuideName is the one I defined.

Use Query Store to Force That Plan

I can easily see the plans for the a given object in the Query Store:

SELECT CAST(qsp.query_plan AS XML),
       qsq.query_id,
       qsp.plan_id
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.AddressByCity');

If I look at the plan that is stored in Query Store, I’ll see the identical plan up above, including the PlanGuideDB and PlanGuideName properties.

So, let’s force the plan using the values returned from the query above:

EXEC sys.sp_query_store_force_plan 6,7;

Now, when we run the query, we’ll see both the plan guide in use and that the plan is forced (see this earlier blog post explaining this behavior). This is all expected behavior.

Drop The Plan Guide

One of the key points to plan forcing that you need to understand is that you can’t force a plan that is invalid for the query. For example, a query that selects from TableA can’t be forced on a query that selects from TableB. In theory then, if I drop the guide, that plan in question is no longer valid. Let’s test this:

EXEC sys.sp_control_plan_guide @operation = N'DROP', @name = SniffFix;

Before I run the query, just to be sure that we replace the plan in cache, I’m also going to clear the cache completely:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Now any plan we get by executing the query will be a new plan in cache. However, the question is, what plan will we see? Is it going to be the plan we would expect from the value of ‘London’, which is different than the plan we’re getting? I would expect this behavior because with the plan guide removed, that plan is now “invalid” since there is no plan guide. Instead, will it be the plan we’re forcing, but somehow without the plan guide still running things?

The only way to find out is to try it. Here’s the plan I got after clearing the cache:

If that looks familiar, that’s because it’s the same. What about the properties?

There are several things to note here. First and foremost, you’ll note that the PlanGuide* properties are gone. Because we dropped the plan guide, this is not a surprise. Next, note the Parameter Compiled Value property. We no longer see ‘Mentor’ as the value here and instead see ‘London’. However, the plan in use is the plan generated based on the value of ‘Mentor’.

Conclusion

What we have is a plan being forced, but one without the need of the plan guide that originally defined it. In short, you can create a plan using a plan guide. You then force that plan. Finally, chuck the plan guide. The plan forcing will still work. Further, the plan you originally forced still shows the plan guide. That plan didn’t change.

I don’t think this is going to be useful for most of us, most of the time. However, I could envision a circumstance where we want to ensure that a particular plan gets into cache, so we select that behavior using the plan guide. Then, with that plan in the Query Store, we force the plan to ensure it’s use. Finally, we toss the guide since it has served it’s purpose. Maybe.

What do you think?