Question From Office Hours: SQL Handle vs. Plan Handle

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!

Previous Post
How to Start Troubleshooting Parameter Sniffing Issues
Next Post
Query Tuning Week: What’s the Difference Between Locking and Blocking and Deadlocking?

7 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!

  • 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.

      • SQL Server Performance Tune
        August 19, 2016 5:11 am

        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.

  • yup, i had it wrong–thanks everyone for setting me straight. =)

Menu
{"cart_token":"","hash":"","cart_data":""}