sys.dm_exec_plan_attributes (Transact-SQL)

Se aplica a:SQL Server

Devuelve una fila por cada atributo del plan especificado por el identificador de plan. Puede usar esta función con valores de tabla para obtener detalles acerca de un plan determinado, como los valores de las claves de la caché o el número de ejecuciones simultáneas del plan.

Nota:

Parte de la información devuelta a través de esta función se asigna a la vista de compatibilidad con versiones anteriores sys.syscacheobjects .

Sintaxis

sys.dm_exec_plan_attributes ( plan_handle )  

Argumentos

plan_handle
Identifica de forma exclusiva un plan de consulta de un lote que se ha ejecutado y cuyo plan reside en la memoria caché del plan. plan_handle es varbinary(64). El identificador del plan se puede obtener de la vista de administración dinámica de sys.dm_exec_cached_plans .

Tabla devuelta

Nombre de la columna	Tipo de datos	Descripción
atributo	varchar(128)	Nombre del atributo asociado a este plan. En la tabla inmediatamente debajo de esta se enumeran los posibles atributos, sus tipos de datos y sus descripciones.
value	sql_variant	Valor del atributo asociado a este plan.
is_cache_key	bit	Indica si el atributo se utiliza como parte de la clave de búsqueda en caché para el plan.

En la tabla anterior, el atributo puede tener los siguientes valores:

Attribute	Tipo de datos	Descripción
set_options	int	Indica los valores de las opciones con las que se compiló el plan.
objectid	int	Una de las claves principales utilizadas para buscar un objeto en la caché. Este es el identificador de objeto almacenado en sys.objects para objetos de base de datos (procedimientos, vistas, desencadenadores, etc.). Con los planes de tipo "ad hoc" o preparados, es un valor hash interno del texto del lote.
dbid	int	Es el Id. de la base de datos que contiene la entidad a la que el plan hace referencia. Con los planes "ad hoc" o preparados, es el Id. de la base de datos desde la que se ejecuta el lote.
dbid_execute	int	En el caso de los objetos del sistema almacenados en la base de datos de recursos , el identificador de base de datos desde el que se ejecuta el plan almacenado en caché. En todos los demás casos es 0.
user_id	int	Un valor de -2 indica que el lote enviado no depende de la resolución implícita de nombres y puede compartirse entre distintos usuarios. Este es el método preferido. Cualquier otro valor representa el Id. del usuario que envía la consulta en la base de datos.
language_id	smallint	Es el Id. del idioma de la conexión que creó el objeto de caché. Para obtener más información, consulte sys.syslanguages (Transact-SQL).
date_format	smallint	Formato de fecha de la conexión que creó el objeto de caché. Para más información, vea SET DATEFORMAT (Transact-SQL).
date_first	tinyint	Valor de la fecha. Para más información, vea SET DATEFIRST (Transact-SQL).
compat_level	tinyint	Representa el nivel de compatibilidad establecido en la base de datos en cuyo contexto se compiló el plan de consulta. El nivel de compatibilidad devuelto es el nivel de compatibilidad del contexto de base de datos actual para las instrucciones adhoc y no se ve afectado por la sugerencia de consulta QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n. Para las instrucciones contenidas en un procedimiento almacenado o función, corresponde al nivel de compatibilidad de la base de datos en la que se crea el procedimiento almacenado o la función.
status	int	Bits de estado interno que son parte de la clave de búsqueda de caché.
required_cursor_options	int	Opciones de cursor especificadas por el usuario, como el tipo de cursor.
acceptable_cursor_options	int	Opciones de cursor a las que SQL Server puede convertir implícitamente para admitir la ejecución de la instrucción. Por ejemplo, el usuario puede especificar un cursor dinámico, pero el optimizador de consultas puede convertir este tipo de cursor a estático.
merge_action_type	smallint	El tipo de plan de ejecución de desencadenadores usados como resultado de la instrucción MERGE. 0 indica un plan sin desencadenadores, un plan de desencadenadores que no se ejecuta como resultado de una instrucción MERGE o un plan de desencadenadores que se ejecuta como resultado de una instrucción MERGE que solo especifica una acción DELETE. 1 indica un plan de desencadenadores INSERT que se ejecuta como resultado de una instrucción MERGE. 2 indica un plan de desencadenadores UPDATE que se ejecuta como resultado de una instrucción MERGE. 3 indica un plan de desencadenadores DELETE que se ejecuta como resultado de una instrucción MERGE que contiene la correspondiente acción INSERT o UPDATE. Para los desencadenadores anidados que se ejecutan por acciones en cascada, este valor es la acción de la instrucción MERGE que provocó la cascada.
is_replication_specific	int	Representa que la sesión desde la que se compiló este plan es aquella que se conecta a la instancia de SQL Server mediante una propiedad de conexión no documentada que permite al servidor identificar la sesión como una creada por los componentes de replicación, de modo que el comportamiento de determinados aspectos funcionales del servidor se cambie según lo que espera dicho componente de replicación.
optional_spid	smallint	La conexión session_id (spid) forma parte de la clave de caché para reducir el número de recompilaciones. Esto impide que las recompilaciones de una sola sesión vuelvan a usar un plan que implique tablas temporales no enlazadas dinámicamente.
optional_clr_trigger_dbid	int	Solo se rellena en el caso de un desencadenador DML clR. Identificador de la base de datos que contiene la entidad. Para cualquier otro tipo de objeto, devuelve cero.
optional_clr_trigger_objid	int	Solo se rellena en el caso de un desencadenador DML clR. Identificador de objeto almacenado en sys.objects. Para cualquier otro tipo de objeto, devuelve cero.
parent_plan_handle	varbinary(64)	Siempre es NULL.
is_azure_user_plan	tinyint	1 para las consultas ejecutadas en una instancia de Azure SQL Database desde una sesión iniciada por un usuario. 0 para las consultas que se han ejecutado desde una sesión no iniciada por un usuario final, sino mediante aplicaciones que se ejecutan desde la infraestructura de Azure que emiten consultas con otros fines de recopilación de telemetría o ejecución de tareas administrativas. Los clientes no se cobran por los recursos consumidos por las consultas en las que is_azure_user_plan = 0. Solo Azure SQL Database .
inuse_exec_context	int	Número de lotes en ejecución que usan el plan de consulta.
free_exec_context	int	Número de contextos de ejecución almacenados en caché para el plan de consulta que no se usa en ese momento.
hits_exec_context	int	Número de veces que el contexto de ejecución se obtuvo de la caché del plan y se reutilizó, ahorrando la sobrecarga de volver a compilar la instrucción SQL. El valor es un agregado para todas las ejecuciones de lotes hasta el momento.
misses_exec_context	int	Número de veces que un contexto de ejecución podría no encontrarse en la caché del plan, provocando la creación de un nuevo contexto de ejecución para la ejecución del lote.
removed_exec_context	int	Número de contextos de ejecución que se han quitado debido a la presión de memoria en el plan almacenado en caché.
inuse_cursors	int	Número de lotes en ejecución que contienen uno o varios cursores que usan el plan almacenado en caché.
free_cursors	int	Número de cursores inactivos o libres para el plan almacenado en caché.
hits_cursors	int	Número de veces que un cursor inactivo se obtuvo del plan almacenado en caché y se volvió a utilizar. El valor es un agregado para todas las ejecuciones de lotes hasta el momento.
misses_cursors	int	Número de veces que un cursor inactivo no se pudo encontrar en la caché.
removed_cursors	int	Número de cursores que se han quitado debido a la presión de memoria en el plan almacenado en caché.
sql_handle	varbinary(64)	Identificador SQL para el lote.

Permisos

En SQL Server, se requiere el permiso VIEW SERVER STATE.

En los objetivos de servicio de Azure SQL Database Basic, S0 y S1, y para las bases de datos de grupos elásticos, se requiere la cuenta de administrador del servidor o la cuenta de administrador de Microsoft Entra. En todos los demás objetivos de servicio de SQL Database, el VIEW DATABASE STATE permiso es necesario en la base de datos.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VER ESTADO DE RENDIMIENTO DEL SERVIDOR en el servidor.

Comentarios

Opciones de Set

Las copias del mismo plan compilado pueden diferir solo por el valor de la columna set_options . Esto indica que las diferentes conexiones usan conjuntos distintos de opciones SET para la misma consulta. El uso de conjuntos de opciones distintos no suele ser aconsejable porque puede ocasionar compilaciones adicionales, una menor reutilización de los planes y la inflación de la caché de los planes debido a que hay varias copias de los planes en la caché.

Evaluar las opciones de Set

Para traducir el valor devuelto en set_options a las opciones con las que se compiló el plan, reste los valores del valor de set_options , empezando por el valor más grande posible, hasta llegar a 0. Cada valor que reste se corresponde con una opción que se usó en el plan de consulta. Por ejemplo, si el valor de set_options es 251, las opciones con las que se compiló el plan son ANSI_NULL_DFLT_ON (128), QUOTED_IDENTIFIER (64), ANSI_NULLS(32), ANSI_WARNINGS (16), CONCAT_NULL_YIELDS_NULL (8), Plan paralelo(2) y ANSI_PADDING (1).

Opción	Valor
ANSI_PADDING	1
ParallelPlan Indica que las opciones de paralelismo del plan han cambiado.	2
FORCEPLAN	4
CONCAT_NULL_YIELDS_NULL	8
ANSI_WARNINGS	16
ANSI_NULLS	32
QUOTED_IDENTIFIER	64
ANSI_NULL_DFLT_ON	128
ANSI_NULL_DFLT_OFF	256
NoBrowseTable Indica que el plan no usa una tabla de trabajo para implementar una operación FOR BROWSE.	512
TriggerOneRow Indica que el plan contiene la optimización de una fila para las tablas delta de desencadenadores AFTER.	1024
ResyncQuery Indica que la consulta fue enviada por procedimientos almacenados del sistema internos.	2048
ARITH_ABORT	4096
NUMERIC_ROUNDABORT	8192
DATEFIRST	16384
DATEFORMAT	32768
LanguageID	65536
UPON Indica que la opción de base de datos PARAMETERIZATION se estableció en FORCED cuando se compiló el plan.	131072
ROWCOUNT	Se aplica a: SQL Server 2012 (11.x) y versiones posteriores 262144

Cursores

Los cursores inactivos se almacenan en caché en un plan compilado para que los usuarios que usan simultáneamente los cursores puedan volver a utilizar la memoria usada para almacenar el cursor. Por ejemplo, suponga que un lote declara y usa un cursor sin cancelar su asignación. Si hay dos usuarios ejecutando el mismo lote, habrá dos cursores activos. Una vez cancelada la asignación de los cursores (posiblemente en lotes diferentes), la memoria usada para almacenar el cursor se almacena en caché y no se libera. La lista de cursores inactivos se conserva en el plan compilado. La siguiente vez que un usuario ejecute el lote, la memoria del cursor almacenado en caché se volverá a usar y se inicializará de forma apropiada como un cursor activo.

Evaluar las opciones de los cursores

Para traducir el valor devuelto en required_cursor_options y acceptable_cursor_options a las opciones con las que se compiló el plan, reste los valores del valor de columna, empezando por el valor más grande posible, hasta alcanzar 0. Cada valor que reste se corresponde con una opción de cursor que se usó en el plan de consulta.

Opción	Valor
None	0
INSENSITIVE	1
SCROLL	2
READ ONLY	4
FOR UPDATE	8
LOCAL	16
GLOBAL	32
FORWARD_ONLY	64
KEYSET	128
DYNAMIC	256
SCROLL_LOCKS	512
OPTIMISTIC	1024
STATIC	2048
FAST_FORWARD	4096
IN PLACE	8192
FOR select_statement	16384

Ejemplos

A Devolver los atributos de un plan concreto

En el ejemplo siguiente se devuelven todos los atributos de un plan especificado. La vista de administración dinámica sys.dm_exec_cached_plans se consulta primero para obtener el identificador del plan especificado. En la segunda consulta, sustituya <plan_handle> por el valor del identificador del plan de la primera consulta.

SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype   
FROM sys.dm_exec_cached_plans;  
GO  
SELECT attribute, [value], is_cache_key  
FROM sys.dm_exec_plan_attributes(<plan_handle>);  
GO  

B. Devolver las opciones SET para los planes compilados y el identificador SQL para los planes almacenados en caché

En el ejemplo siguiente se devuelve un valor que representa las opciones con las que se compiló cada plan. Además, se devuelve el identificador SQL para todos los planes en caché.

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  

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_cached_plans (Transact-SQL)
sys.databases (Transact-SQL)
sys.objects (Transact-SQL)