What is the Difference Between sql_handle and plan_handle?- Interview Question of the Week #269

Question: What is the Difference Between sql_handle and plan_handle?

What is the Difference Between sql_handle and plan_handle?- Interview Question of the Week #269 plan_handle1-800x390

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.

What is the Difference Between sql_handle and plan_handle?- Interview Question of the Week #269 plan_handle

Here are a few additional blog posts which you may find interesting on the same topic:

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)

SQL Cache, SQL DMV, SQL Memory, SQL Scripts, SQL Server
Previous Post
How to Decode @@OPTIONS Value? – Interview Question of the Week #268
Next Post
How to Check Database Performance Facets in SQL Server? – Interview Question of the Week #270

Related Posts

Leave a Reply