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

In my previous post about Query Store, you saw that the persistence of forced plans does not imply that they are used. This post brings another example where forced plans are still in the system, with no failures, but they are not applied against the desired query.

For this demo, we need a database that is a part of the availability group. Within a database we need only one sample table, one user table type, and one stored procedure:

DROP TABLE IF EXISTS dbo.T;
CREATE TABLE dbo.T(
id INT IDENTITY(1,1) NOT NULL,
c1 INT NOT NULL,
c2 INT NULL,
CONSTRAINT PK_T PRIMARY KEY CLUSTERED (id)
);
GO
INSERT INTO dbo.T(c1)
SELECT 1 + ABS(CHECKSUM(NEWID())) % 10000
FROM dbo.GetNums(100000);
GO


CREATE TYPE dbo.Tvp AS TABLE(
Id INT NOT NULL PRIMARY KEY CLUSTERED
)
GO
CREATE OR ALTER PROCEDURE dbo.GetList
(@tvp AS dbo.Tvp READONLY)
AS
SELECT t.*
FROM dbo.T t
INNER JOIN @tvp tvp ON t.id=tvp.Id;
GO

Let’s ensure that Query Store is empty and turned on:

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

Now, we can execute our sample query multiple times:

DECLARE @t AS dbo.Tvp;
INSERT @t SELECT TOP (10) id FROM dbo.T ORDER BY 1 DESC;
EXEC dbo.GetList @t;
GO 3

The execution is fast, with a Nested Loop join based execution plan. Let’s suppose we want to be sure that this plan will be used for all further calls, and we want to achieve this by forcing the plan. This is not always a good idea, but in this demo, we want to have any forced plan. When we execute the query again, we should see in Query Store something like this:

post1
The plan forcing is respected, all fine.

One of the main advantages of Query Store is that data belongs to the database. Thus, it should survive server crashes and failovers. In case of a failover, we should see a similar screen when we run the query on another server, which takes the role of the primary server after the failover. After the failover, I executed the same query again (against a new primary):

DECLARE @t AS dbo.Tvp;
INSERT @t SELECT TOP (10) id FROM dbo.T ORDER BY 1 DESC;
EXEC dbo.GetList @t;
GO 3

When I looked at the Query Store report, I saw no new executions for this query:

post2.png

I checked then the sys.query_store_query DMV and figured out, why these executions are not captured:

SELECT * FROM sys.query_store_query WHERE query_hash = (SELECT query_hash FROM sys.query_store_query WHERE query_id = 35);



post3

Query Store has instantiated new query_id (57) for my query, and executions are associated to it:

post21

Let’s look closer at the batch_sql_handle attribute. In the following figure, you can see the only difference:

post4.png

The selected number are hex numbers with value of 20 and 16 respectively. Where is this number coming from? This is the database_id attribute. And yes, in this example, my databases have different database_ids:

post5

We have again a situation where query_id has been changed for a query that we did not touch at all, this time because of different database_id attributes on primary and secondary.

Conclusion

When you force a plan for a query that uses table variables or table-valued parameters, you have to check frequently whether it is still in use, since the uniqueness of query_id is very weak and can be affected even if you did not change the code.

Thanks for reading.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: