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
GOIf 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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
