sys.dm_exec_plan_attributes (Transact-SQL)

Se aplica a:yesSQL Server (todas las versiones compatibles)

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 siguiente 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:

Atributo 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. En el caso de 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 Las opciones de cursor que SQL Server pueden convertir implícitamente en 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 ha conectado 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 El session_id de conexión (spid) forma parte de la clave de caché para reducir el número de recompilaciones. Esto evita la recompilación de una sola sesión de 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 un 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 para 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 Azure SQL Database objetivos de servicio Básico, S0 y S1, y para bases de datos en grupos elásticos, se requiere la cuenta de administrador del servidor o la cuenta de administrador de Azure Active Directory. En todos los demás objetivos de servicio SQL Database, el VIEW DATABASE STATE permiso es necesario en la base de datos.

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 alcanzar 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 32 768
LanguageID 65536
UPON

Indica que la opción de base de datos PARAMETERIZATION se estableció en FORCED cuando se compiló el plan.
131 072
ROWCOUNT Se aplica a: SQL Server 2012 (11.x) a SQL Server 2019 (15.x)

262 144

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 Value
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)