sys.dm_exec_cached_plans (Transact-SQL)

Artículo
12/19/2023

Se aplica a: SQL Server Azure SQL Database Azure SQL Instancia administrada Azure Synapse Analytics Analytics Platform System (PDW)

Devuelve una fila para cada plan de consulta almacenado en caché por SQL Server para una ejecución de consulta más rápida. Puede usar esta vista de administración dinámica para ver los planes de consulta 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é.

En Azure SQL Database, las vistas de administración dinámica no pueden mostrar información que afecte a la contención de la base de datos o que exponga datos acerca de otras bases de datos a las que el usuario tenga acceso. Para evitar exponer esta información, se filtran todas las filas que contienen datos que no pertenecen al inquilino conectado. Además, los valores de las columnas memory_object_address y pool_id se filtran; el valor de columna se establece en NULL.

Nota:

Para llamar a esto desde Azure Synapse Analytics o Analytics Platform System (PDW), use el nombre sys.dm_pdw_nodes_exec_cached_plans. El grupo de SQL sin servidor no admite esta sintaxis en Azure Synapse Analytics.

Nombre de la 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 memoria 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 memoria 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 memoria 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.
refcounts	int	Número de objetos de caché que hacen referencia a este objeto de caché. Los recuentos de referencia deben ser al menos 1 para que una entrada esté en la memoria caché.
usecounts	int	Número de veces que se ha buscado el objeto de caché. Este número no se incrementa cuando las consultas con parámetros encuentran un plan en la memoria caché. Se puede incrementar varias veces cuando se utiliza un plan de representación.
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 desglose de memoria del plan almacenado en caché y con sys.dm_os_memory_cache_entries_entries para obtener el costo de almacenar en caché la entrada.
cacheobjtype	nvarchar(34)	Tipo del objeto en la memoria caché. El valor puede ser uno de los siguientes: Plan compilado Código auxiliar del plan compilado Árbol de análisis Procedimiento extendido Función compilada CLR Procedimiento compilado CLR
objtype	nvarchar(16)	Tipo de objeto. A continuación se muestran los valores posibles y sus descripciones correspondientes. Procedimiento: procedimiento almacenado Preparado: Instrucción preparada Adhoc: consulta ad hoc. Hace referencia a Transact-SQL enviado como eventos de lenguaje mediante osql o sqlcmd en lugar de como llamadas a procedimientos remotos. ReplProc: Replication-filter-procedure Trigger: desencadenador Vista: Vista Valor predeterminado: valor predeterminado UsrTab: Tabla de usuario SysTab: Tabla del sistema Comprobación: restricción CHECK Regla: Regla
plan_handle	varbinary(64)	Identificador del plan en memoria. Este identificador es transitorio y permanece constante solo mientras el plan permanece en la memoria 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}pool_id{2}	int	El identificador del grupo de recursos de servidor considerado para este uso de memoria del plan.
pdw_node_id	int	Se aplica a: Azure Synapse Analytics, Sistema de la plataforma de análisis (PDW) Identificador del nodo en el que se encuentra esta distribución.

Permisos

En SQL Server y SQL Managed Instance, requiere el permiso VIEW SERVER STATE.

En los objetivos de servicio de SQL Database Basic, S0 y S1, y para las bases de datos de grupos elásticos, se requiere la cuenta de administrador del servidor, la cuenta de administrador de Microsoft Entra o la pertenencia al rol de ##MS_ServerStateReader## servidor. En el resto de objetivos del servicio de SQL Database, se requiere el permiso VIEW DATABASE STATE en la base de datos o la pertenencia en el rol del servidor ##MS_ServerStateReader##.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VER ESTADO DE RENDIMIENTO DEL SERVIDOR 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 sql_handle se devuelve para el plan. El operador PIVOT se usa para generar los set_options atributos y sql_handle como columnas en lugar de como filas. Para obtener más información sobre el valor devuelto en set_options, vea sys.dm_exec_plan_attributes (Transact-SQL).

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 memoria caché.

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,   
    omo.memory_object_address, 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

Consulte también

Funciones y vistas de administración dinámica (Transact-SQL)
Funciones y vistas de administración dinámica relacionadas con ejecuciones (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL)
sys.dm_exec_plan_attributes (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_os_memory_objects (Transact-SQL)
sys.dm_os_memory_cache_entries (Transact-SQL)
FROM (Transact-SQL)

Compartir vía