sys.dm_exec_cached_plans (Transact-SQL)

Aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Analítica (PDW)Base de datos SQL en Microsoft Fabric

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 exponer información que afecte a la contención de la base de datos ni exponer información sobre otras bases de datos a las que el usuario tiene 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.

Permisos

SQL Server 2019 (15.x) y versiones anteriores requieren VIEW SERVER STATE permiso.

SQL Server 2022 (16.x) y versiones posteriores y Azure SQL Managed Instance requieren VIEW SERVER PERFORMANCE STATE permiso.

En los objetivos de servicio básico de Azure SQL Database, 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##.

Ejemplos

A Devuelve el texto por lotes de las entradas almacenadas 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;

B. Devolver planes de consulta para 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';

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

En el ejemplo siguiente se devuelven las SET opciones con las que se compiló el plan. También sql_handle se devuelve para el plan. El PIVOT operador 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.

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;

D. Devolver el desglose de 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
     INNER 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';

Contenido relacionado

• Vistas de administración dinámica del sistema
• 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)
• Cláusula FROM más JOIN, APPLY, PIVOT (Transact-SQL)