Difference between sql_handle and plan_handle

Leave a comment (0) Go to comments

After reading a post about DBCC FREEPROCCACHE with parameters, I got  a lot of question like

  • What is sql_handle?
  • what is plan_handle?
  • How to find sql_handle and plan_handle?
  • What is the difference between a sql_handle and plan_handle?
  • Can we have a example to understand sql_handle and plan_handle ?

Let me answer all these question,

A sql_handle contains a hash of all the text in entire group of SQL statements or "Batch". This indicates that executing two identical add-hoc queries will result in one sql_handle,  where as a plan_handle is a hash value derived from the compiled plan for a group of SQL statements or "Batch".  It uniquely identifies the Query Plan (steps the SQL Server’s optimization engine has created for executing the SQL). Confused ???? Let me describe this using a example and a screen shot and a video tutorial.

A sql_handle contains a hash of all the text in entire group of SQL statements or "Batch". This indicates that executing two identical add-hoc queries will result in one sql_handle. This is, in fact, the case.   Using different upper or lower case will result in a different sql_handle, however.  For example, starting off your query with "Select" will result in a different sql_handle than the all lower-case "select".  Interestingly, white spaces and different parameters (when compared using the equals operator) do not always result in a different plan as illustrated by the following SQL code:


 

DBCC freeproccache;

GO

SELECT * FROM   HumanResources.Employee WHERE  BusinessEntityID = 1

GO

SELECT * FROM   HumanResources.Employee WHERE  BusinessEntityID = 5

GO

SELECT st.TEXT, qs.sql_handle,qs.plan_handle FROM

sys.dm_exec_query_stats qs CROSS APPLY sys.Dm_exec_sql_text(sql_handle) st; 

 

Output of the following TSQL code, which gives you a sql_handle and plan_handle of a particular SQL Code.

 

Difference between sql handle and plan handle sql handle Vs. plan handle sql handle and plan handle sql handle sql server plan handle and sql handle plan handle how to find a sql handle how to find a plan handle Difference between sql handle and plan handle

 

A plan_handle is a hash value derived from the compiled plan for a group of SQL statements or "Batch".  It uniquely identifies the Query Plan (steps the SQL Server’s optimization engine has created for executing the SQL).  Included in the plan_handle value is a complex type composed of bits representing some of the Set Options (ANSI_NULLS, ANSI_PADDING, ARITH_ABORT, etc.), an objectid, etc. You can see all the parts that make up that portion of a plan_handle by substituting a plan_handle in the following and looking at rows with an is_cache_key of 1:

SELECT *FROM sys.Dm_exec_plan_attributes (paste your plan handle here)

There is a one-to-many relationship between sql_handle and sql_plan. Identical SQL will result in the same sql_handle, but differences in set options, etc. can result in different Query Plans and therefore different plan_handle values.

Related Post :

Executing DBCC FREEPROCCACHE with parameters

EOF - Difference between sql_handle and plan_handle, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.