Great question!
We recently added some columns to sp_BlitzCache to help you remove undesirable plans from the cache. Doing this will force SQL to come up with a new plan, or you know, just re-create the old plan. Because that just happens sometimes. I answered it, but I wasn’t happy with my answer. So here’s a better one!
Handles and Hashes
SQL Server stores a whole mess of different things used to identify queries. Query Hash, Query Plan Hash, SQL Handle, Plan Handle, so on and so forth until eventually you get a collision. They identify different things differently, but let’s answer the question rather than get bogged down in pedantics.
SQL Handle is a hash of the SQL text. This includes comments, white space, different casing, etc. It will be unique for each batch.
Plan Handle is a hash of the execution plan. Both of them are stored per batch, but this one isn’t guaranteed to be unique. You can have one SQL Handle and many Plan Handles.
Query Hash and Query Plan Hash are a little bit different. They’re explained really well over here.
Thanks for reading!
11 Comments. Leave new
There was a broken link to a tool called Query Hash Statistics. Have you ever used this tool or was this before Query Store? Thanks!
Never used it. Not sure.
should the above be rendered as:
Plan Handle is a hash of the execution plan. Both of them are stored per batch, but this one isn’t guaranteed to be unique. You can have *one Plan* Handle and *many SQL* Handles.
?
Pretty sure he meant what he said. Adding just an extra space or changing case on an alias etc. would result in a different SQL handle, but the execution plan created for both could be the same, therefore the same Plan Handle.
Think so, too.
http://www.sommarskog.se/query-plan-mysteries.html#intocache
>> ”
The DMV (Dynamic Management View) sys.dm_exec_query_stats has one entry for each query currently in the plan cache. If a procedure has multiple statements, there is one row per statement. Of interest here is sql_handle and plan_handle. I use sql_handle to determine which procedure the cache entry relates to (later we will see examples where we also retrieve the query text) so that we can filter out all other entries in the cache. Most often you use plan_handle to retrieve the query plan itself, and we will see an example of this later, but in this query I access a DMV that returns the attributes of the query plan. More specifically, I return the attributes that are cache keys. When there is more than entry in the cache for the same procedure, the entries have at least one difference in the cache keys. A cache key is a run-time setting, which for one reason or another calls for a different query plan. Most of these settings are controlled with a SET command, but not all.
” <<
The same stored procedure (SQL) could get compiled into different plans because of difference in cache keys, like the SET options.
You’re all right! You all win hugs.
yup, i had it wrong–thanks everyone for setting me straight. =)
As the sql_handle is a hash of the exact query text it will use this hash for the current query to see if the same query with a generated execution plan already exists. If the hash exists it simply uses the execution plan and doesnt duplicate the sql_handle hash. Therefore it is and should remain unique.
I think you’re implying multiple plans for the same SQL text can’t exist. If so, that’s incorrect – for more info, Google for Erland’s excellent post, Slow in the App, Fast in SSMS.
We use a number of tools to collect telemetry data around the performance of our application and they are not directly related to each other. We have one tool specifically for database performance and a more general APM looking at distributed traces, but the SQL Server analysis isn’t great (hence the other tool).
I created a POC to add a ‘CorrelationId’ in the form of a comment into my SQL Queries, so the tools that are capturing the applications executed SQL statements would have the same ids and we could correlate transactions in one to database performance details in the other. The POC works, but…
The downside appears to be the comment before or after the SQL statement in the query plan cache forms part of the plan_handle and sql_handle columns when the text in each is hashed. I was hoping that comments would be ignored and it turns out this is not the case as I am getting separate plans for every call to the database.
Correct, every variation in text (including casing & spacing) can result in a different plan.