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
)