Tag - Table variables

Dude, Where’s My Forced Plan?! – Part 1

Query Store is a great performance troubleshooting tool. I like it; in the past three years, it saved me a lot of time and money. It allows you to quickly identify exec plan regressions. By forcing an old plan, you can almost instantly implement a workaround for the issue and buy a time necessary for the final fix. Forcing plan acts (should act) as “a temporary solution” for plan regression issues. But until you finally fix the underlying issue, it should be considered as a solution:  a temporary, but stable and steadily solution. However, forcing plans does not always work. You can find interesting articles about plan forcing in the excellent series of Query Store articles written by Erin Stellato (blog | twitter). In this article, I won’t write about forcing plan failures, where a plan fails because of missing index or objects, but describe a situation where everything seems to be fine – no failure at all – but it does not work.

To demonstrate the issue, we need to create a simple user table type and use it in a stored procedure in the WideWorldImporters sample database.

CREATE TYPE dbo.IntList AS TABLE(
fValue INT NOT NULL PRIMARY KEY CLUSTERED
)
GO
CREATE OR ALTER PROCEDURE dbo.GetOrderList
(@tvp AS dbo.IntList READONLY)
AS
SELECT o.*
FROM Sales.Orders o
INNER JOIN @tvp t on o.OrderID=t.fValue;
GO

It’s very simple: we’ll invoke the stored procedure to get order details for a given list of order ids. Let’s ensure that Query Store is empty and turned on:

ALTER DATABASE WideWorldImporters SET QUERY_STORE CLEAR;
ALTER DATABASE WideWorldImporters SET QUERY_STORE = OFF;
GO
ALTER DATABASE WideWorldImporters
SET QUERY_STORE = ON (QUERY_CAPTURE_MODE = AUTO);
GO

Now, run the following code that calls the stored procedure with 10 order Ids, and after clearing the cache calls it again, but this time with 2000 order ids:

DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (10) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO 3
DBCC FREEPROCCACHE;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (2000) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;

We expect here two plans: a Nested Loop Join based one for the first query and a Merge or Hash Join plan for the low selective second query. Let’s look at the Query Store to see, how this stored procedure executes. Since Query Store works with queries rather than with procedures, we need to identify the query_id of our SELECT statement:

SELECT qs.query_id, q.query_sql_text, qs.query_hash, qs.batch_sql_handle
FROM sys.query_store_query AS qs
INNER JOIN sys.query_store_query_text AS q ON qs.query_text_id = q.query_text_id
WHERE object_id = OBJECT_ID('dbo.GetOrderList')
ORDER BY qs.last_execution_time DESC

I got the value 1 for the query_id  (in your case it could be different, depending on your workload). Here is what we can see in the Tracking query report:

P1


P2

As we expected, we see two different plans: Nested Loop Join for 10 Ids and Merge Join for 2000 order ids.

Let’s assume now that we want to have the Nested Loop Join plan for our query, and that we have no time or access to the code to achieve this otherwise than forcing the plan. The assumption is not realistic for this simple query, but it’s reasonable in case of complex queries with complex plans on production system. Let’s force the lower plan and run the following code:

DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (10) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (2000) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO

What we expect to see now is the same execution plan for both executions. Let’s check in the Query Store report:

P3

We see that SQL Server reused the same plan for both calls, exactly one that we forced. All fine.

What would happen, when we clear the cache and call the stored procedure with parameters in the opposite order, i.e:

DBCC FREEPROCCACHE;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (2000) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (10) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO

Let’s observe the Query Store report:

P4

What we see now is the third execution plan. The plan that we forced is not reused this time, SQL Server has created a new one. But this plan is also a Nested Loop join plan; it reflects the shape defined by forcing the plan. When you look at its XML representation, you would find the UsePlan attribute with the value True. Since there was no plan in cache, a new one is created, with the same shape but different estimations. Although it’s not exactly the same plan, it is still the plan we want to see – a Nested Loop Join based one. Still all fine – forcing plan works.

Let’s imagine now that we rollout a new version of the application. There were no changes in our procedure, but somebody included it in the installation and this statement has been executed.

CREATE OR ALTER PROCEDURE dbo.GetOrderList
(@tvp AS dbo.IntList READONLY)
AS
SELECT o.*
FROM Sales.Orders o
INNER JOIN @tvp t on o.OrderID=t.fValue;
GO

From a business logic point of view, there are no changes, altering a procedure with the same text should only mean that the new plan will be generated for it. And this should not be a problem because with forcing plan we ensured that the “good plan” will be used. Let’s confirm this by executing the following code:

DBCC FREEPROCCACHE;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (2000) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO 10
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (10) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO 10

Let’s have a look at the Query Store:

P5

When you observe this screenshot, you might think – hey dude, it seems you used the old screenshot, it does not show new executions! No, I did not, I refreshed the report, the report is correct. But, where are the recent 20 executions? Well, let’s execute again the statament looking for query_id:

P6

Ooops… Our SELECT query got a new query_id! And since a forced plan is associated not to a particular query, but to a given query_id, it’s clear why it does not affect the execution. The forcing plan is correctly configured – there is no forced plan failure, it is just waiting for execution of the query with the query_id 1. If this would happen, it would work correctly, but since a new query_id is associated with our query, the forced plan has nothing to do with it. We can confirm this by observing the Query Store report:

P7

You can see that all 20 executions are done with the Merge Join based execution plan, although we have forced the plan and our query was not changed at all!

Forced plans are stable and will remain in the system only if query_id does not change. And query_id depends on the following five parameters:

  • query_hash  – hash value of query text
  • object_id of the object (e.g. stored procedure) where the query has been executed (0 for standalone queries)
  • context_settings_id (e.g. ANSI standard, etc.)
  • query_parameterization_type – indicating how the query is parameterized
  • batch_sql_handle –  ID of the statement batch the query is part of. Populated only if the query references temporary tables or table variables

Since we call the stored procedure by using table variables, the batch_sql_handle attribute has been changed after we issued the ALTER PROCEDURE statement. Here is a query that returns two queries in the Query Store for the same query text:

P8

You can see that the values in these two rows are slightly different and this small difference canceled all our efforts with forcing plans and revitalized our issue.

Conclusion

Be very careful with forcing plans against queries referencing table variables and table-valued parameters. The query_id attribute for such queries is very unstable and forcing plans even as a workaround could have a very short lifetime. You should use a different approach to fix such issues.

Thanks for reading.