This is question I’ve gotten a few times in class…Can you force a plan for a different query with Query Store?
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…
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
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
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.
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!