sys.dm_exec_plan_attributes

Artículo
12/15/2008

Actualizado: 12 de diciembre de 2006

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

Nombre de columna

Tipo de datos

Descripción

attribute

varchar(128)

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

Atributo

Tipo de datos

Descripción

set_optionsintIndica los valores de las opciones con las que se compiló el plan.

objectidintUna 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.

dbidintEs el Id. de la base de datos donde reside la entidad para la que tenemos el plan. Con los planes "ad hoc" o preparados, es el Id. de la base de datos desde la que se ejecuta el lote.

dbid_executeintPara 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_idintId. del esquema que contiene el objeto. 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.

language_idsmallintEs 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_formatsmallintFormato de fecha de la conexión que creó el objeto de caché. Para obtener más información, vea SET DATEFORMAT (Transact-SQL).

date_firsttinyintValor de la fecha. Para obtener más información, vea SET DATEFIRST (Transact-SQL).

statusintBits de estado interno que son parte de la clave de búsqueda de caché.

required_cursor_optionsintOpciones de cursor especificadas por el usuario, como el tipo de cursor.

acceptable_cursor_optionsintOpciones 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. Para obtener más información, vea Usar conversiones de cursor implícitas.

inuse_exec_contextintNúmero de lotes en ejecución que usan el plan de consulta. Para obtener más información acerca del contexto de ejecución y los planes de consulta, vea Almacenar en caché y volver a utilizar un plan de ejecución.

free_exec_contextintNúmero de contextos de ejecución almacenados en caché para el plan de consulta que no se usa en ese momento.

hits_exec_contextintNú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_contextintNú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_contextintNúmero de contextos de ejecución que se han quitado debido a la presión de la memoria en el plan almacenado en caché.

inuse_cursorsintNúmero de lotes en ejecución que contienen uno o varios cursores que usan el plan almacenado en caché.

free_cursorsintNúmero de cursores inactivos o libres para el plan almacenado en caché.

hits_cursorsintNú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_cursorsintNúmero de veces que un cursor inactivo no se pudo encontrar en la caché.

removed_cursorsintNúmero de cursores que se han quitado debido a la presión de la memoria en el plan almacenado en caché.

sql_handlevarbinary(64)Identificador SQL para el lote.

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.

Notas

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é. Para obtener más información, vea Recomendaciones para optimizar consultas.

Evaluar las opciones de Set

Para traducir el valor devuelto en set_options para 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 los desencadenadores AFTER e INSTEAD OF. Es decir, si sólo hay una fila afectada en las tablas inserted o deleted, se establece esta opción. Se aplica a los desencadenadores Transact-SQL y a los desencadenadores CLR que realizan un acceso de datos en proceso.	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

Permisos

Requiere el permiso VIEW SERVER STATE en el servidor.

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 texto SQL y el identificador SQL.

SELECT plan_handle, pvt.set_options, pvt.sql_handle, text
FROM (
    SELECT plan_handle, epa.attribute, epa.value, st.text 
    FROM sys.dm_exec_cached_plans 
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE cacheobjtype = 'Compiled Plan') AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
GO

Vea también

Referencia

Funciones y vistas de administración dinámica
Funciones y vistas de administración dinámica relacionadas con ejecuciones
sys.dm_exec_cached_plans
sys.databases (Transact-SQL)
sys.objects (Transact-SQL)

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios

Versión	Historial
12 de diciembre de 2006	Contenido nuevo: Se han enumerado los atributos devueltos en la columna Attribute. Se han agregado las secciones Notas y Ejemplos.

Compartir a través de