sys.dm_exec_cached_plan_dependent

sys.dm_exec_cached_plan_dependent_o...

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

SQL Server 2005 Books Online (November 2008)

sys.dm_exec_cached_plan_dependent_objects

New: 14 April 2006

Returns a row for each Transact-SQL execution plan, common language runtime (CLR) execution plan, and cursor associated with a plan.

Note:
For Transact-SQL execution plans, the sys.dm_exec_cached_plan_dependent_objects table-valued function maps to the syscacheobjects system table in SQL Server 2000.

Syntax

dm_exec_cached_plan_dependent_objects(plan_handle)

Arguments

plan_handle

Uniquely identifies a query execution plan for a batch that has executed and its plan resides in the plan cache. plan_handle is varbinary(64). The plan_handle can be obtained from the following dynamic management objects:

Table Returned

Column name	Data type	Description
usecounts	int	Number of times the execution context or cursor has been used. Column is not nullable.
memory_object_address	varbinary(8)	Memory address of the execution context or cursor. Column is not nullable.
cacheobjtype	nvarchar(50)	Possible values are Executable plan CLR compiled function CLR compiled procedure Cursor Column is not nullable.

Column name

Data type

Description

usecounts

int

Number of times the execution context or cursor has been used.

Column is not nullable.

memory_object_address

varbinary(8)

Memory address of the execution context or cursor.

Column is not nullable.

cacheobjtype

nvarchar(50)

Possible values are

Executable plan
CLR compiled function
CLR compiled procedure
Cursor

Column is not nullable.

Permissions

Requires VIEW SERVER STATE permission on the server.

Physical Joins

Relationship Cardinalities

From	To	On	Relationship
dm_exec_cached_plan_dependent_objects	dm_os_memory_objects	memory_object_address	One-to-one

Reference

Help and Information

Getting SQL Server 2005 Assistance