I was eating dinner with Hugo Kornelis and we started talking about query hash values. You know, like everyone does at dinner. As we talked about it, I suddenly thought about both Plan Guides and the Query Store. I wondered what happened to the query hash values in that case? Thus are blog posts born.

Query Hash and Plan Guides

The behavior of the query hash itself is fairly straight forward. The text of the query is run through a hashing algorithm within SQL Server and a value comes out, so these two queries:

SELECT *
FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
   ON sod.SalesOrderID = soh.SalesOrderID;
SELECT *
FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
   ON sod.SalesOrderID = soh.SalesOrderID
OPTION (FORCE ORDER);

Result in two different query hash values:

Unhinted Query Hash

0x7264738ED060F3C1

Hinted Query Hash

0xD763CBB6B860AFF3

Things get interesting if we create a Plan Guide for the first query to make it behave like the second query:

EXEC sys.sp_create_plan_guide @name = 'HashTest', -- sysname
                              @stmt = N'SELECT *
FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
   ON sod.SalesOrderID = soh.SalesOrderID;',
                              -- nvarchar(max)
                              @type = N'SQL', -- nvarchar(60)
                              @module_or_batch = NULL, -- nvarchar(max)
                              @params = NULL, -- nvarchar(max)
                              @hints = N'OPTION(FORCE ORDER)'; -- nvarchar(max)

If we rerun the query and then take a look at the first operator in the execution plan, we can see that the Plan Guide is in use… and that the query hash has changed. It no longer matches the original query. Now it matches the query that included the query hint. This actually makes perfect sense. The Plan Guide is basically changing the query from the first example above, into the second.

Now, what happens when we toss in the Query Store

Query Hash and Query Store

Traditionally, in the Query Store, forcing a plan isn’t changing anything except the plan, so what you’ll see is the original query hash value. However, things get a little off when we start forcing plans on queries that have Plan Guides. I’ve blogged about this before, you’ll get the plan from Query Store, but you’ll get the properties showing that both the Plan Guide and plan forcing are both in use. Let’s force the plan and see what happens:

EXEC sys.sp_query_store_force_plan 135,114;

Are you sitting down?

What I expected would happen is that we would see the query hash value go back to the original value. However, look at this screen capture from the SELECT operator of my execution plan:

I’ve highlighted three pieces of information. You can see that a Plan Guide was applied because there is a ‘PlanGuideName’ property and value at the top. You can see that a plan was forced because we see the ‘Use plan’ property and value at the bottom. However, in the middle you can see, that the query hash, for some strange reason, now matches the plan hash.

What’s Going On?

I don’t know.

Clearly, the behavior, at least in all the testing I’ve done and read about, is that plan forcing overrides Plan Guides. Fine, but clearly there’s some housekeeping around the Plan Guides that is incomplete. Part of that is right there in the query hash value. SQL Server can’t use the value from the Plan Guide text because it’s wrong, but it must not generate a new value. Rather than leaving it blank (although, why not leave it blank), it copies the query plan hash value.

What’s this mean for production servers and your day-to-day existence? Not much… unless you’re querying for the query hash value out of the DMVs while using Plan Guides and Query Store with Plan Forcing. In that case, you may have some issues tracking down everything you expect to see under normal circumstances.

The post Query Hash Values, Plan Guides and the Query Store appeared first on Grant Fritchey.