Can you force a plan for a different query with Query Store?

This is question I’ve gotten a few times in class…Can you force a plan for a different query with Query Store?

tl;dr

No.

Assume you have two similar queries, but they have different query_id values in Query Store.  One of the queries has a plan that’s stable, and I want to force that plan for the other query.  Query Store provides no ability to do this in the UI, but you can try it with the stored procedure.  Let’s take a look…

Testing

Within WideWorldImporters we’ll execute an ad-hoc query with two different input values:

USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON;
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

USE [WideWorldImporters];
GO

DECLARE @CustomerID INT;
SET @CustomerID = 972;

SELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity
FROM Sales.Orders o
JOIN Sales.OrderLines ol
ON o.OrderID = ol.OrderID
WHERE o.CustomerID = @CustomerID;
GO

DECLARE @CustomerID2 INT;
SET @CustomerID2 = 972;

SELECT o.ContactPersonID, o.OrderDate, ol.StockItemID, ol.Quantity
FROM Sales.Orders o
JOIN Sales.OrderLines ol
ON o.OrderID = ol.OrderID
WHERE o.CustomerID = @CustomerID2;
GO

Let’s see what’s in Query Store:


SELECT qt.query_text_id, q.query_id, qt.query_sql_text, p.plan_id, TRY_CAST(p.query_plan AS XML)
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p
ON q.query_id = p.query_id
WHERE qt.query_sql_text LIKE '%Sales.Orders%';
GO

Query information from Query Store
Query information from Query Store

We see that we have two different queries and one plan for each. We can force the plan for the first query:

EXEC sp_query_store_force_plan @query_id = 3, @plan_id = 3;
GO

This works.  If we try to force that same plan for the other query:

EXEC sp_query_store_force_plan @query_id = 4, @plan_id = 3;
GO
Error when trying to force a different plan for a query
Error when trying to force a different plan for a query

Trying to force plan_id 3 for query_id 4 throws this error:

Msg 12406, Level 11, State 1, Procedure sp_query_store_force_plan, Line 1 [Batch Start Line 34]
 Query plan with provided plan_id (2) is not found in the Query Store for query (4). Check the plan_id value and rerun the command.

Summary
Within Query Store, the relationship between query_id and plan_id is managed internally (i.e. there are no foreign key constraints for the underlying tables), and there is a validation that any plan_id that you want to force for a query_id must have been generated for that specific query.

In this type of scenario, you have to get the plan shape you want for the query, which may require trying different input parameters.  The example I’ve provided is very simple, but when in doubt, check the input parameters for the plan that you want, then try those with the other query (that doesn’t yet have the plan you want to force).  Of course, if you have to use a query or index hint to get the plan that you want, then it’s going to be a little trickier to get the plan you want for the original query.  Good luck!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.