sys.dm_exec_plan_attributes (Transact-SQL)

Статья
05/27/2014

Возвращает по одной строке для каждого атрибута плана, ассоциированного с планом, заданным посредством дескриптора плана. Функция с табличным значением может использоваться для получения подробных сведений об определенном плане, например значения ключа кэша или количество одновременных текущих выполнений плана.

Примечание

Часть сведений, возвращаемых этой функцией, сопоставляется с представлением обратной совместимости sys.syscacheobjects.

Применимо для следующих объектов: SQL Server (начиная с SQL Server 2008 до текущей версии).

Синтаксис

sys.dm_exec_plan_attributes ( plan_handle )

Аргументы

plan_handle
Уникально идентифицирует план запроса для запущенного пакета, план которого хранится в кэше планов. Аргумент plan_handle имеет тип varbinary(64). Дескриптор плана можно получить из динамического административного представления sys.dm_exec_cached_plans.

Возвращаемая таблица

атрибут

varchar(128)

Имя атрибута, ассоциированного с этим планом. Одно из следующих:

Атрибут	Тип данных	Описание
set_options	int	Показывает значения параметров, с использованием которых был скомпилирован план.
objectid	int	Одно из основных ключевых слов, используемое для поиска объекта в кэш-памяти. Это идентификатор объекта, хранимый в таблице sys.objects базы данных (процедуры, представления, триггеры и т. п.). Для планов типа «Нерегламентированный» или «Подготовленный» — это внутренний хэш текста пакета.
dbid	int	Идентификатор базы данных, содержащей сущность, к которой относится план. Для нерегламентированных и подготовленных планов это идентификатор базы данных, из которой выполняется пакет.
dbid_execute	int	Для системных объектов, хранимых в базе данных Resource, представляет собой идентификатор базы данных, из которой выполняется план в кэш-памяти. Во всех остальных случаях это значение равно 0.
user_id	int	Значение «-2» означает, что представленный пакет не зависит от неявного разрешения имен и может совместно использоваться разными пользователями. Этот метод является предпочтительным. Любое другое значение обозначает идентификатор пользователя, отправившего запрос к базе данных.
language_id	smallint	Идентификатор языка соединения, в результате которого был создан объект кэша. Дополнительные сведения см. в разделе sys.syslanguages (Transact-SQL).
date_format	smallint	Формат даты соединения, во время которого был создан объект кэша. Дополнительные сведения см. в разделе SET DATEFORMAT (Transact-SQL).
date_first	tinyint	Значение первой даты. Дополнительные сведения см. в разделе SET DATEFIRST (Transact-SQL).
status	int	Биты внутреннего состояния, являющиеся частью ключа уточняющего запроса к кэшу.
required_cursor_options	int	Параметры курсора, указанные пользователем, такие как тип курсора.
acceptable_cursor_options	int	Параметры курсора, которые SQL Server может неявно преобразовывать для поддержания выполнения инструкции. Например, пользователь может указать динамический курсор, но оптимизатор запросов может преобразовать этот тип курсора в статический.
inuse_exec_context	int	Количество выполняемых в данный момент пакетов, использующих план запроса.
free_exec_context	int	Количество контекстов выполнения в кэш-памяти для плана запроса, которые не используются в данный момент.
hits_exec_context	int	Количество получений контекста выполнения из кэш-памяти планов и его повторных использований, приводящее к снижению издержек на повторную компиляцию инструкции SQL. Это значение является статистическим для всех пакетов, выполняющихся в настоящий момент.
misses_exec_context	int	Количество обнаружений отсутствия контекста выполнения в кэш-памяти планов, приводящее к созданию нового контекста выполнения для пакета выполнения.
removed_exec_context	int	Количество контекстов выполнения, которые были удалены по причине слишком активного использования памяти для плана в кэш-памяти.
inuse_cursors	int	Количество выполняемых в данный момент пакетов, содержащих один или более курсоров, использующих план в кэш-памяти.
free_cursors	int	Количество бездействующих или свободных курсоров для плана в кэш-памяти.
hits_cursors	int	Количество получений неактивного курсора из плана в кэш-памяти и его повторных использований. Это значение является статистическим для всех пакетов, выполняющихся в настоящий момент.
misses_cursors	int	Количество случаев обнаружения отсутствия неактивного курсора в кэш-памяти.
removed_cursors	int	Количество курсоров, которые были удалены по причине слишком активного использования памяти для плана в кэше.
sql_handle	varbinary(64)	Дескриптор SQL для пакета.
merge_action_type	smallint	Тип плана выполнения триггеров, используемого в результате инструкции MERGE. 0 указывает план без триггеров, или план триггеров, который не выполняется в результате инструкции MERGE, или план триггеров, который выполняется в результате инструкции MERGE, в которой задано только действие DELETE. 1 указывает план триггеров INSERT, который выполняется в результате инструкции MERGE. 2 указывает план триггеров UPDATE, который выполняется в результате инструкции MERGE. 3 указывает план триггеров DELETE, который выполняется в результате инструкции MERGE, содержащей соответствующее действие INSERT или UPDATE. Для вложенных триггеров, выполняемых каскадными операциями, это значение является действием инструкции MERGE, запустившей каскад.

value

sql_variant

Значение атрибута, ассоциированного с этим планом.

is_cache_key

bit

Указывает, используется ли атрибут в качестве части ключа уточняющего запроса к кэшу для плана.

Разрешения

Требует разрешения VIEW SERVER STATE на сервере.

Замечания

Параметры SET

Копии одного и того же скомпилированного плана могут отличаться только значением в столбце set_options. Это указывает на то, что разные соединения используют разные наборы параметров SET для одного запроса. Использование разных наборов параметров, как правило, нежелательно, поскольку приводит к дополнительным компиляциям, меньшему повторному использованию планов и расширению кэша планов по причине размещения нескольких копий планов в кэш-памяти.

Оценка параметров SET

Чтобы преобразовать возвращенное в столбце set_options значение в параметры, с использованием которых был скомпилирован план, необходимо вычитать эти значения из значения set_options, начиная с максимально возможного значения, до получения значения 0. Каждое вычитаемое значение соответствует одному параметру, который использовался в плане запроса. Например, если значение set_options равно 251, то параметрами, с использованием которых был скомпилирован план, были ANSI_NULL_DFLT_ON (128), QUOTED_IDENTIFIER (64), ANSI_NULLS(32), ANSI_WARNINGS (16), CONCAT_NULL_YIELDS_NULL (8), Parallel Plan(2) и ANSI_PADDING (1).

Параметр	Значение
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 Указывает, что план не использует рабочую таблицу для реализации операции FOR BROWSE.	512
TriggerOneRow Указывает, что план содержит однострочную оптимизацию для таблиц разности триггеров AFTER.	1024
ResyncQuery Указывает, что запрос был направлен внутренней системной хранимой процедурой.	2048
ARITH_ABORT	4096
NUMERIC_ROUNDABORT	8192
DATEFIRST	16384
DATEFORMAT	32768
LanguageID	65536
UPON Указывает, что параметру базы данных PARAMETERIZATION присвоено значение FORCED при компиляции плана.	131072
ROWCOUNT	Область применения: SQL Server 2012 в SQL Server 2014 262144

Курсоры

Неактивные курсоры кэшируются в скомпилированном плане так, чтобы одновременно работающие пользователи курсоров могли повторно использовать память, использованную для хранения курсора. Предположим, что пакет объявляет и использует курсор без его освобождения. Если два пользователя выполняют один и тот же пакет, то будет два активных курсора. После освобождения курсоров (потенциально в разных пакетах), память, используемая для хранения курсора, кэшируется и не освобождается. Этот список неактивных курсоров хранится в скомпилированном плане. При следующем выполнении пакета пользователем память кэшированного курсора будет использоваться повторно и инициализироваться соответствующим образом, как для активного курсора.

Оценка параметров курсора

Чтобы выделить из значений, возвращенных в столбцах required_cursor_options и acceptable_cursor_options, параметры, с использованием которых был скомпилирован план, необходимо вычитать эти значения из значения столбца, начиная с максимально возможного значения, до получения значения 0. Каждое вычитаемое значение соответствует одному курсору, который использовался в плане запроса.

Параметр	Значение
Нет	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

Примеры

А.Возврат атрибутов для конкретного плана

Следующий пример возвращает все атрибуты для указанного плана. В первый раз динамическое административное представление sys.dm_exec_cached_plans опрашивается для получения дескриптора указанного плана. Во втором запросе <plan_handle> заменяется значением дескриптора плана из первого запроса.

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

Б.Возврат параметров SET для скомпилированных планов и дескриптора SQL для планов в кэш-памяти

Следующий пример возвращает значение, представляющее параметры, с использованием которых был скомпилирован план. Кроме того, возвращается дескриптор SQL для всех кэшированных планов.

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