sys.dm_exec_plan_attributes (Transact-SQL)

Artículo
05/19/2015

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 con esta función se asigna a la vista sys.syscacheobjects compatible con las versiones anteriores.

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 de tipo varbinary(64). El identificador del plan se puede obtener desde la vista de administración dinámica sys.dm_exec_cached_plans.

Tabla devuelta

atributo

varchar(128)

Nombre del atributo asociado a este plan. Uno de los siguientes:

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é. Es el Id. del objeto almacenado en sys.objects para los objetos de base de datos (procedimientos, vistas, desencadenadores, etcétera). 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	Para los objetos del sistema almacenados en la base de datos Resource, es el Id. 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. Éste 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, vea sys.syslanguages (Transact-SQL).
date_format	smallint	Formato de fecha de la conexión que creó el objeto de caché. Para obtener más información, vea SET DATEFORMAT (Transact-SQL).
date_first	tinyint	Valor de la fecha. Para obtener más información, vea SET DATEFIRST (Transact-SQL).
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 que SQL Server puede convertir de forma implícita para permitir 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.
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.
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.

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.

Permisos

Requiere el permiso VIEW SERVER STATE en el servidor.

Comentarios

Opciones de Set

Las copias del mismo plan compilado podrían diferir únicamente en 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 en las opciones con las que se compiló el plan, reste los valores del valor set_options, comenzando con el mayor valor posible, hasta que llegue 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), Parallel Plan(2) y ANSI_PADDING (1).

Opción	Valor
ANSI_PADDING	1
Parallel Plan	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

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 para las opciones con las que se compiló el plan, reste los valores del valor de la columna, comenzando con el mayor valor posible, hasta que llegue a 0. Cada valor que reste se corresponde con una opción de cursor que se usó en el plan de consulta.

Opción	Valor
Ninguna	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