Database Reference
In-Depth Information
As you already saw, the sys.dm_exec_cached_plans view provides information about every plan stored in the
SQL and Object plan cache stores. The key column in the view is plan_handle , which uniquely identifies the plan.
In the case of a batch, that value remains the same even when some statements from the batch are recompiled. In
addition to plan_handle , this view provides information about the type of plan (Compiled Plan, Compiled Plan Stub,
and so forth) in the cacheobjtype column, type of object (Proc, Ad-Hoc query, Prepared, Trigger, and so on) in the
objtype column, reference and use counts, memory size, and a few other attributes.
The Data Management Function sys.dm_exec_plan_attributes accepts plan_handle as a parameter and
returns a set of the attributes of a particular plan. Those attributes include references to the database and object to
which the plan belongs, user_id of the session that submits the batch, and quite a few other attributes.
One of the attributes, sql_handle , links plan to the batch to which plan has been compiled. You can use it
together with the sys.dm_exec_sql_text function to obtain its SQL text.
Each attribute has a flag if it is included in the cache key . SQL Server reuses plans only when both the sql_handle
and cache key of the cached plan match the values from the submitted batch. Think about the set_option attribute
as an example. It is included in the cache key and, therefore, different SET options would lead to different cache key
values, which would prevent plan reuse.
One SQL batch, identified by sql_handle , can have multiple plans, identified by plan_handle —one for each
cache key attributes value. Listing 26-31 illustrates an example of this.
Listing 26-31. SQL_Handle and plan_handle relations
dbcc freeproccache
go
set quoted_identifier off
go
select top 1 ID from dbo.Employees where Salary > 40000
go
set quoted_identifier on
go
select top 1 ID from dbo.Employees where Salary > 40000
go
;with PlanInfo(sql_handle, plan_handle, set_options)
as
(
select pvt.sql_handle, pvt.plan_handle, pvt.set_options
from
(
select p.plan_handle, pa.attribute, pa.value
from
sys.dm_exec_cached_plans p with (nolock) outer apply
sys.dm_exec_plan_attributes(p.plan_handle) pa
where cacheobjtype = 'Compiled Plan'
) as pc
pivot (max(pc.value) for pc.attribute
IN ("set_options", "sql_handle")) AS pvt
)
 
Search WWH ::




Custom Search