Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 122

Compiled Plan Prepared 2
Compiled Plan Proc 14
Extended Proc Proc 2
Parse Tree View 13
At this point you can see that there are four more ad hoc compiled plans, and a number of other
new cached objects. The objects you are interested in here are the ad hoc plans.
Run the following T-SQL to get the SQL text and the plan handle for the T-SQL query you ran
against the AdventureWorks2012 database:
select p.refcounts, p.usecounts, p.plan_handle, s.text
from sys.dm_exec_cached_plans as p
cross apply sys.dm_exec_sql_text (p.plan_handle) as s
where p.cacheobjtype = 'compiled plan'
and p.objtype = 'adhoc'
order by p.usecounts desc
This should provide something similar to the results shown in Figure 5-4.
FIGURE 5-4
To see the execution context, take the plan _ handle that you got from the preceding results and
plug it into the DMF sys . dm _ exec _ cached _ plan _ dependent _ objects , as shown in the following
example:
select *
from sys.dm_exec_cached_plan_dependent_objects
(0x06000F005163130CB880EE0D000000000000000000000000)
The preceding code returned the following results:
USECOUNTS MEMORY_OBJECT_ADDRESS CACHEOBJTYPE
1 0x0DF8A038 Executable Plan
Another interesting thing you can examine are the attributes of the plan. These are found in the
DMF sys . dm _ exec _ plan _ attributes ( plan _ handle ) Note that you need to pass the DMF a plan
handle, and then you will get the attributes for that plan: