sql_handle and plan_handle （读书笔记）

The sys.dm_exec_cached_plan dmv contains a column called a plan_handle for every compiled plan. The plan_handle is a hashed value that derives from compiled plan of entire batch, and it is guaranteed to be unique for every currently exist compiled plan. The plan_handle can be used as an identifier for a compiled plan

 

The actual SQL Test of a batch or objects is stored in another cache called the SQL Manager Cache(SQLMGR). The sql test associated with each batch is stored in its entirety, including all the comments. And it's can be retrieved using a data value called sql_handle. The sql_handle contains a hash of the entire batch text , and because it is unique for every batch, the sql_handle can serve as a identifier for the batch text in the SQLMGR cache .

 

For specific sql test, it always has the same sql_handle, but it may not always have the same plan_handle. If the cache keys change, we'll get a new plan_handle in plan cache. The relationship between sql_handle and plan_handle is thus 1:N

 

The following example indicates that one sql_handle maps two plan_handle because the SET option changed.

dbcc freeproccache

go

SET CONCAT_NULL_YIELDS_NULL off

go

select top 1 null+'aa' from sys.objects

go

SET CONCAT_NULL_YIELDS_NULL on

go

select top 1 null+'aa' from sys.objects

go

select COUNT(plan_handle),sql_handle from sys.dm_exec_query_stats group by sql_handle

go