sys.dm_exec_cached_plans

Actualizado: 12 de diciembre de 2006

Devuelve una fila para cada plan de consulta que SQL Server almacena en caché para agilizar la ejecución. Puede usar esta vista de administración dinámica para ver los planes de consultas almacenados en caché, el texto de las consultas almacenadas en caché, la cantidad de memoria que ocupan los planes y el contador de reutilización de los planes almacenados en caché.

[!NOTA] Con los planes de consultas, la vista de administración dinámica sys.dm_exec_cached_plans se asigna a la tabla del sistema syscacheobjects en SQL Server 2000.

Nombre de columna

Tipo de datos

Descripción

bucketid

int

Identificador del depósito de hash en el que se almacena en caché la entrada. El valor indica un intervalo comprendido entre 0 y el tamaño de la tabla hash para el tipo de caché.

En la caché de planes SQL y planes de objetos, el tamaño de la tabla hash puede ser de hasta 10007 en sistemas de 32 bits y de hasta 40009 en sistemas de 64 bits. En la caché de árboles enlazados, el tamaño de la tabla hash puede ser de hasta 1009 en sistemas de 32 bits y de hasta 4001 en sistemas de 64 bits. En la caché de procedimientos almacenados extendidos, el tamaño de la tabla hash puede ser de hasta 127 en sistemas de 32 y de 64 bits. Para obtener más información acerca de los tipos de caché y de las tablas hash, vea sys.dm_os_memory_cache_hash_tables.

refcounts

int

Número de objetos de caché que hacen referencia a este objeto de caché. Refcounts debe ser al menos 1 para que haya una entrada en la caché.

usecounts

int

Número de veces que se ha usado el objeto en caché desde su origen.

size_in_bytes

int

Número de bytes consumidos por el objeto de caché.

memory_object_address

varbinary(8)

Dirección de memoria de la entrada de caché. Este valor se puede usar con sys.dm_os_memory_objects para obtener el análisis de la memoria del plan almacenado en caché y con sys.dm_os_memory_cache_entries para obtener el costo de almacenar en caché la entrada.

cacheobjtype

nvarchar(34)

Tipo del objeto en la caché. El valor puede ser uno de los siguientes:

• Plan compilado
• Árbol de análisis
• Procedimiento extendido
• Función compilada CLR
• Procedimiento compilado CLR

objtype

nvarchar(16)

Tipo de objeto. El valor puede ser uno de los siguientes:

Valor

Descripción

ProcProcedimiento almacenado

PreparedInstrucción preparada

AdhocConsulta ad hoc1

ReplProcProcedimiento de filtro de réplica

TriggerDesencadenador

ViewVista

DefaultOpción predeterminada

UsrTabTabla de usuario

SysTabTabla del sistema

CheckRestricción CHECK

RuleRegla

plan_handle

varbinary(64)

Identificador del plan en memoria. Este identificador es transitorio y permanece constante sólo cuando el plan permanece en la caché. Este valor se puede usar con las siguientes funciones de administración dinámica:

sys.dm_exec_sql_text

sys.dm_exec_query_plan

sys.dm_exec_plan_attributes

1  Hace referencia a Transact-SQL enviado como eventos de lenguaje mediante osql o sqlcmd, en vez de llamadas a un procedimiento remoto.

Permisos

Requiere el permiso VIEW SERVER STATE en el servidor.

Ejemplos

A. Devolver el texto del lote de las entradas en caché que se reutilizan

En el ejemplo siguiente se devuelve el texto SQL de todas las entradas en caché que se han usado más de una vez.

SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 1 
ORDER BY usecounts DESC;
GO

B. Devolver los planes de consulta de todos los desencadenadores almacenados en caché

En el ejemplo siguiente se devuelven los planes de consulta de todos los desencadenadores almacenados en caché.

SELECT plan_handle, query_plan, objtype 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
WHERE objtype ='Trigger';
GO

C. Devolver las opciones SET con las que se compiló el plan

En el ejemplo siguiente se devuelven las opciones SET con las que se compiló el plan. También se devuelve el valor sql_handle del plan. El operador PIVOT se usa para conseguir los atributos set_options y sql_handle como columnas en lugar de como filas. Para obtener más información acerca del valor devuelto en set_options, vea sys.dm_exec_plan_attributes.

SELECT plan_handle, pvt.set_options, pvt.sql_handle
FROM (
      SELECT plan_handle, epa.attribute, epa.value 
      FROM sys.dm_exec_cached_plans 
      OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
      WHERE cacheobjtype = 'Compiled Plan'
      ) AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
GO

D. Devolver el análisis de la memoria de todos los planes compilados almacenados en caché

En el ejemplo siguiente se devuelve un análisis de la memoria que usan todos los planes compilados de la caché.

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject, 
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes 
FROM sys.dm_exec_cached_plans AS ecp 
JOIN sys.dm_os_memory_objects AS omo 
    ON ecp.memory_object_address = omo.memory_object_address 
    OR ecp.memory_object_address = omo.parent_address
WHERE cacheobjtype = 'Compiled Plan';
GO

Vea también

Referencia

Funciones y vistas de administración dinámica
Funciones y vistas de administración dinámica relacionadas con ejecuciones
sys.dm_exec_query_plan
sys.dm_exec_plan_attributes
sys.dm_exec_sql_text
sys.dm_os_memory_objects
sys.dm_os_memory_cache_entries

Otros recursos

Usar APPLY

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios