This is post 3 in the series about query fingerprints. Here’s the previous posts in this series:
What is it
A query plan hash is a hash made from the operations in a specific execution plan. I’d like to say that every single execution plan has a different hash, but there might be one or two hash collisions out there.
Inside the execution plan, the specific costs per operator, amount of memory, and other resources values can change without changing the query plan hash. I’ll demo that later in this post.
Let’s get right into the examples. First, the same query but different query plan hashes
Using the StackOverflow 2010 database, this is the same query from the query hash post, but I’m going to mix it up a little bit.
This query is a good example because the parameter of 3 will use a nonclustered index I created on PostTypeId. When I change the parameter to 1, the query will scan the Posts clustered index because there’s much more data for this parameter.
--PostTypeId 3 (Wiki posts in StackOverflow)
SELECT *
FROM Posts
WHERE PostTypeId = 3
Okay, our first query plan hash is 0xC296E1ED384FC09A, and the query hash is 0xF039BCB7AB95E111.
Now, I need a different execution plan, so I’ll change the value 3 to 1.
--PostTypeId 1 (Questions in StackOverflow)
SELECT *
FROM Posts
WHERE PostTypeId = 1
The query hash is the same, 0xF039BCB7AB95E111, but the execution plan is different. Now the query is just scanning the clustered index and the new query plan hash is 0x25255CFFBD7EC1FD.
What if we change the parameter but the execution plan is the same? Will the query plan hash change?
Here’s another parameter that will generate the same execution plan shape as the first query, but with different costs and values.
--PostTypeId 4 (TagWikiExerpt)
SELECT *
FROM Posts
WHERE PostTypeId = 4
So we have the same query hash as the other two examples, and the same query plan hash as the first example. This time, with a slightly higher costs on the key lookup.
The morale of using the query plan hash
Often, people will talk about finding a “bad plan” in cache when they’re looking at poor performance and suspect that it’s due to parameter sniffing.
Here’s my take: Since one query can have more than one query plan, there could be many plan hashes. Any type of index change can also influence what plan is chosen, and change the query plan hash.
My recommendations
I recommend tracking and collecting the most expensive queries in your plan cache over time, and as part of that strategy, monitor for new query plan hashes on existing queries.
Thanks for reading! Stay tuned for the last part in this series, plan handle.