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