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