Question: What is the Difference Between sql_handle and plan_handle?
Answer: I was recently asked this question during the Comprehensive Database Performance Health Check. Before we see the answer to this question, let us see where actually you can find the columns sql_handle and plan_handle.
If you run the following query you will find the columns sql_handle and plan_handle.
SELECT sql_handle, plan_handle FROM sys.dm_exec_query_stats
SQL Handle: It is a hash of the SQL Text containing all of your formatting (casing, spaces, etc).
Plan Handle: It is a hash of the execution plan build from a SQL.
In simple words: sql_handle is the source code, and the plan_handle is the compiled object code.
Please note that the relationship between SQL Handle : Plan Handle is 1:N, which means you can have one select statement and can also have multiple plans handles for the same SQL. When your set options changes or configuration changes for any SQL handles, your plan can also change. Additionally, there can be many reasons why for a single SQL text, there are multiple plans.
You can check that by running the following query.
SELECT sql_handle, COUNT(plan_handle) TotalPlans FROM sys.dm_exec_query_stats GROUP BY sql_handle ORDER BY TotalPlans DESC GO
If you want to additionally see the SQL Text from the query, you can also run the following query and display a cached plan for any query here.
SELECT COUNT(qs.plan_handle) TotalPlans,st.text, qs.sql_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st GROUP BY st.text, qs.sql_handle ORDER BY TotalPlans DESC GO
Trust me, I use this query very frequently during my Comprehensive Database Performance Health Check as it helps me to identify if there are any queries or T-SQL which continuously builds a new plan and pollutes my cache.
Here are a few additional blog posts which you may find interesting on the same topic:
- SQL SERVER – List Query Plan, Cache Size, Text and Execution Count
- SQL SERVER – Finding The Oldest Query Plan From Cache
- SQL SERVER – Plan Cache and Data Cache in Memory
- SQL SERVER – Cleanup Plan Cache For a Single Database
What are the different queries do you run when your system is running slow? Please leave a comment and I will share that on the blog with due credit to you. You can also reach out to me via twitter.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
