This blog is a follow-up to a discussion I recently had on Twitter, started by Patrick Joliffe asking some people: if you need to force an execution plan, would you prefer to do it through Query Store or through a Plan Guide?

Most people agreed that Query Store should be preferred. Not only because it is easier to use, but also (perhaps I should say mainly) because it provides better insight and visibility. The last thing you want to do is to lose track of what queries you have forced an execution plan for. After all, you really need to regularly check whether the forcing is still needed, and whether you are not missing out on potentially better performance if you return control back to the optimizer!

But what about portability?

We did, however, identify one scenario where using a Plan Guide to force an execution plan would be a better choice than using Query Store. And that’s when you have found a plan to force on one instance, and now want to force that same plan for the same query on a different instance, or in a difference database.

Query Store only allows you to force plans that the Query Store has “seen” on that instance, and in that database. If you have a query and you want to force a specific plan, you will need to first ensure that the query runs, at least once, under the right circumstances to create the desired plan, so that the Query Store can capture it. Sometimes that is easy, in which case this blog is not for you.

What if you know you need to force the execution plan, you know you’ll get that execution plan only when a lot of data is already in the database, and you need to ship your software with an empty database to your customers? How do you set it up to force the plan you need?

What if you have a production database where a few plans are forced, and performance tests in your test database do not give proper information because the test database does not force the same execution plan, and you have no idea how to get that same plan in the test database?

In other words … what if you run into a situation where a plan guide would have been a better choice, because you can just create the same plan guide on your other instance, and it will work.

Demo setup

For the example in this blog, I have installed the AdventureWorks2017 sample database on two different instances. In both, I have then created the same stored procedure:

CREATE OR ALTER PROC dbo.GetAddressByCity @City nvarchar(30)
AS
SELECT AddressLine1,
       AddressLine2,
       City,
       ModifiedDate
FROM   Person.Address
WHERE  City = @City;
GO

After creating this stored procedure, I also ensured to enable the Query Store for AdventureWorks2017 on both instances, and to purge all previously captured data (something I would not normally do for production instances, but for simple demos it is a quick and easy way to ensure I can quickly and easily find the correct queries and execution plans).

On instance A, I then executed the query below. Because the stored procedure is sensitive to parameter sniffing, the two executions will result in different execution plans. Both will be captured by the Query Store.

-- This creates and caches an execution plan for a large number of rows
EXEC dbo.GetAddressByCity @City = N'Paris';
GO
-- Clear the procedure cache to force a recompile
DECLARE @planhandle varbinary(64);
SELECT      @planhandle = cp.plan_handle
FROM        sys.dm_exec_cached_plans             AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE       OBJECT_NAME(st.objectid) = N'GetAddressByCity'
OPTION (RECOMPILE);
DBCC FREEPROCCACHE(@planhandle);
GO
-- This creates and caches an execution plan for a small number of rows
EXEC dbo.GetAddressByCity @City = N'Raleigh';
GO

After running this, I used one of the standard reports in the Query Store: View Top Resource Consuming Queries. I found the query for this stored procedure, and it had two execution plans, as expected. I decided to force the query that does a Clustered Index Scan, since this execution plan has more stable performance than the alternative version that uses an Index Scan and a Key Lookup. The picture below shows how it looks in the user interface after forcing the plan. As you can see, the execution plan with plan id 3 (this number might be different for you if you follow along!) is now forced for this query.

After this, I switch to my other instance. The same stored procedure is already created there, and Query Store is active too. Here, we execute a slightly different sample workload.

-- This creates and caches an execution plan for a small number of rows
EXEC dbo.GetAddressByCity @City = N'Raleigh';

After running this, we can find the same query in the Query Store, but it will have only a single execution plan. And not the one we forced on instance A. If we want to force the same execution plan on instance B, then using the Query Store alone will not work.

How to find forced plans in the Query Store

Returning to instance A, we can use the Query Store user interface to find all queries with forced execution plans. But in this case, I prefer to query the data directly from the catalog views, since it gives me the data in an easier to access method. Especially if I ever have to do this for multiple queries.

SELECT     qsp.plan_id,
           qsq.query_id,
           qsqt.query_text_id,
           qsp.query_plan,
           qsq.object_id,
           QUOTENAME(OBJECT_SCHEMA_NAME(qsq.object_id)) + '.'
           + QUOTENAME(OBJECT_NAME(qsq.object_id)) AS ObjectName,
           qsqt.query_sql_text
FROM       sys.query_store_plan       AS qsp
INNER JOIN sys.query_store_query      AS qsq
   ON      qsq.query_id       = qsp.query_id
INNER JOIN sys.query_store_query_text AS qsqt
   ON      qsqt.query_text_id = qsq.query_text_id
WHERE      qsp.is_forced_plan = 1;

So I executed the query above, and got this result set:

The benefit of having a controlled demo environment is, of course, that there is only a single forced query here. But ideally this list should always be short. Especially on a production environment! Forcing an execution plan should be an exception, not the rule.

Copying the forced plans to another instance

I now know the plan I need to force on instance B. I also know that I cannot force it directly. Time to get inventive!

The first step is to fall back to the trusted method of using a Plan Guide. Yes, I know that I said that Query Store is the preferred method. Trust me, we will get this done through the Query Store. But to do so, we need to use a Plan Guide as a temporary solution.

You start with this basic statement on instance B:

EXEC sys.sp_create_plan_guide @name = N'QS_Forced_1', -- Just a name, must be unique!
                              @stmt = N'(to fill)',
                              @type = N'OBJECT',
                              @module_or_batch = N'(to fill)',
                              @hints = N'(to fill)';

This statement as written above is incomplete; trying to execute it will give you an error. You need to copy in some of the data that we returned just before, on instance A, when querying the Query Store catalog views for queries with forced execution plans.

The two last parameters are pretty straightforward. The value for parameter @module_or_batch is a simple copy/paste of the value returned in the ObjectName column, and the value for parameter @hints is a copy/paste of the data returned in the query_plan column. Do make sure to check the settings you have in Management Studio for truncating long columns. If you miss part of the data, you won’t be able to successfully force the plan!

The @stmt parameter might appear to be straightforward too, but it isn’t. The query_sql_text returned from the Query Store catalog views reads: (@City nvarchar(30))SELECT AddressLine1, AddressLine2, City, ModifiedDate FROM Person.Address WHERE City = @City . This is not the original query text, but a parameterized version of it. And that version is considered invalid when you supply it to sp_create_plan_guide. You’ll get an error message if you try. So instead, you need to either remove the first part, or grab the query from the stored procedure. (Note that there are still formatting differences; they do not affect the outcome).

After pasting in all values, and correcting the @stmt parameter as described above, my query looks like this (note that I truncated most of the showplan XML in the @hints paramter):

EXEC sys.sp_create_plan_guide @name = N'QS_Forced_1', -- Just a name, must be unique!
                              @stmt = N'SELECT AddressLine1,         AddressLine2,         City,         ModifiedDate  FROM   Person.Address  WHERE  City = @City',
                              @type = N'OBJECT',
                              @module_or_batch = N'[dbo].[GetAddressByCity]',
                              @hints = N'<ShowPlanXML [...] </ShowPlanXML>';

After executing this, I have successfully created a plan guide on instance B. This also invalidates existing execution plans in the plan cache, so when I execute the stored procedure again, it will recompile. And during that recompile the plan guide will kick in and force the supplied execution plan.

EXEC dbo.GetAddressByCity @City = N'Raleigh';

I executed the above with the option to include the execution plan plus run-time statistics (aka actual execution plan) in the results, and this was the execution plan:

As you can see in the plan properties, and by looking at the plan shape, the plan guide was successfully used to force the plan I wanted for this query. But, indeed, it is forced by using a Plan Guide. Not by using Query Store, as we actually wanted.

Setting it up the way we want

But here’s the catch. Now that we executed the query with a Plan Guide active, we allowed Query Store to see the query, and the preferred execution plan. Which means that at this time, when we open the user interface of the Query Store on instance B, we should be able to see the preferred execution plan. And indeed, that is the case!

The original plan is still there (plan ID 1), but now we also see the desired plan (plan ID 2 – yes, my test instance is really inactive when I’m writing a blog post and not running tests). At this time the Query Store does not force any plans, but all it takes is a single click of the mouse to change that.

After clicking the Force Plan button, we now no longer need the Plan Guide. It served its purpose, now it can go back to the depths of the void where it belongs:

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

And now, even though we no longer have the Plan Guide, we will still always get the plan that we forced when running the stored procedure. The plan forcing through Query Store still works, even though the plan that is forced contains an internal reference to this plan guide (that you can find if you once more query the Query Store catalog views and then search in the showplan XML it returns).

The reason it works is that all plan forcing, regardless of the method used, does not simply brute force the engine into using the plan supplied. If a plan is forced, then the optimizer will still look at the query and compile an execution plan. Except that now, instead of freely searching the plan space for what it believes to be the best plan, it now does a targeted search to find an execution plan that is, as Microsoft people have called it, “morally equivalent” to the plan you try to force.

The XML of the execution plan you see when you execute the query and capture the execution plan plus run-time statistics, or when you capture the execution plan only (aka estimated execution plan) without running it, will not be an exact match of the XML of the execution plan that the Query Store is told to force. The former will definitely not have a reference to the plan guide that now no longer exists. But that is a difference that is ignored for the purpose of determining whether the execution plans are “morally equivalent”, and so we get the exact execution plan we want. And it is forced by the Query Store, not by a Plan Guide, which is what we wanted.

Parting thoughts

In this post I used the Query Store catalog views to find relevant information for a forced plan. Using a Plan Guide as a temporary step, I then ported that forced plan to another instance, allowing me to force the same execution plan for the same query on another instance. The Plan Guide was deleted, the final plan forcing on the second instance is also done through the Query Store.

The example used here was for a query in a stored procedure. The same method can be used, though with some variations, to port forced execution plans for ad-hoc queries to another instance.

Most of the work outlined in this post can, if needed, be scripted. You do need to ensure to make the proper modifications to the query text to get it to work, though. I deliberately have not included any attempt to script this, though. If you need to do this for a handful of execution plans, doing it manually as outlined here is faster than scripting it. And it has the added benefit of more control over what you are doing.

And frankly, if you want to do this for hundreds of forced execution plans, then I suggest that you instead spend the time to investigate why you have so many forced execution plans. You are probably better off fixing the underlying issue, so that you then no longer need to force all those execution plans.