sys.dm_exec_query_plan_stats (Transact-SQL)

Статья
08/21/2023

Область применения: SQL Server 2019 (15.x) База данных SQL Azure Управляемый экземпляр SQL Azure

Возвращает эквивалент последнего известного фактического плана выполнения для ранее кэшированного плана запроса.

Синтаксис

sys.dm_exec_query_plan_stats ( plan_handle )

Аргументы

plan_handle

Маркер, который однозначно определяет план выполнения запроса для пакета, который выполнил и его план находится в кэше планов или в настоящее время выполняется. plan_handle — varbinary(64).

plan_handle можно получить из следующих объектов динамического управления:

Таблица возвращенной информации

Имя столбца	Тип данных	Description
dbid	smallint	Идентификатор базы данных, в контексте которой выполнялась компиляция инструкции Transact-SQL, соответствующей данному плану. Для нерегламентированных и подготовленных инструкций SQL это идентификатор базы данных, в которой происходила компиляция инструкции. Столбец может содержать значение NULL.
objectid	int	Идентификатор объекта (например хранимой процедуры или определяемой пользователем функции) для этого плана запроса. Для нерегламентированных и подготовленных пакетов этот столбец имеет значение NULL. Столбец может содержать значение NULL.
number	smallint	Целое число нумерованных хранимых процедур. Например, группа процедур для приложения заказов может называться orderproc; 1, orderproc; 2 и т. д. Для нерегламентированных и подготовленных пакетов этот столбец имеет значение NULL. Столбец может содержать значение NULL.
encrypted	bit	Указывает, зашифрована ли соответствующая хранимая процедура. 0 = не зашифрована 1 = зашифрована Столбец не допускает значение NULL.
query_plan	xml	Содержит последнее известное представление showplan среды выполнения фактического плана выполнения запроса, указанного с plan_handle. Представление Showplan имеет формат XML. Для каждого пакета, содержащего, например, нерегламентированные инструкции Transact-SQL, вызовы хранимых процедур и вызовы определяемых пользователем функций, формируется один план. Столбец может содержать значение NULL.

Замечания

Это функция, включаемая пользователем. Чтобы включить на уровне сервера, используйте флаг трассировки 2451. Чтобы включить на уровне базы данных, используйте параметр LAST_QUERY_PLAN_STATS в ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Эта системная функция работает под инфраструктурой профилирования статистики выполнения упрощенных запросов. Дополнительные сведения см. в разделе Инфраструктура профилирования запросов.

Выходные данные sys.dm_exec_query_plan_stats Showplan содержат следующие сведения:

Все сведения о времени компиляции, найденные в кэшированном плане
Сведения о среде выполнения, такие как фактическое количество строк на оператора, общее время запроса ЦП и время выполнения, предупреждения о разливе, фактическое DOP, максимальное используемое память и предоставленную память

В следующих условиях выходные данные Showplan, эквивалентные фактическому плану выполнения, возвращаются в query_plan столбце возвращаемой таблицы для sys.dm_exec_query_plan_stats:

План можно найти в sys.dm_exec_cached_plans.

AND
Выполняемый запрос является сложным или ресурсоемким.

В следующих условиях в столбце возвращаемой таблицы в столбце возвращаемой таблицы возвращается query_plan упрощенное ¹ выходные данные Showplan:sys.dm_exec_query_plan_stats

План можно найти в sys.dm_exec_cached_plans.

AND
Запрос достаточно прост, обычно классифицируется как часть рабочей нагрузки OLTP.

¹ Относится к Showplan, который содержит только оператор корневого узла (SELECT).

В следующих условиях выходные данные не возвращаются из sys.dm_exec_query_plan_stats:

План запроса, указанный с помощью plan_handle , был исключен из кэша планов.

ИЛИ
План запроса не был кэшируемым в первую очередь. Дополнительные сведения см. в разделе "Кэширование плана выполнения" и "Повторное использование".

Примечание.

Ограничение в количестве вложенных уровней, разрешенных в типе данных XML , означает, что sys.dm_exec_query_plan не может возвращать планы запросов, которые соответствуют или превышают 128 уровней вложенных элементов. В более ранних версиях SQL Server это условие не позволило возвращать план запроса и генерировать ошибку 6335. В SQL Server 2005 (9.x) с пакетом обновления 2 и более поздних версий query_plan столбец возвращает значение NULL.

Разрешения

Необходимо разрешение VIEW SERVER STATE на сервере.

Разрешения для SQL Server 2022 и более поздних версий

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

Примеры

А. Просмотрите последний известный фактический план выполнения запросов для определенного кэшированного плана

В следующем примере запрашивается sys.dm_exec_cached_plans поиск интересного плана и копирование из plan_handle выходных данных.

SELECT * FROM sys.dm_exec_cached_plans;
GO

Затем, чтобы получить последний известный фактический план выполнения запроса, используйте скопированную plan_handle с системной функцией sys.dm_exec_query_plan_stats.

SELECT * FROM sys.dm_exec_query_plan_stats(< copied plan_handle >);
GO

B. Просмотрите последний известный фактический план выполнения запросов для всех кэшированных планов

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO

В. Просмотрите последний известный фактический план выполнения запроса для определенного кэшированного плана и текста запроса

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.text LIKE 'SELECT * FROM Person.Person%';
GO

D. Просмотр кэшированных событий для триггера

SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle)
WHERE objtype ='Trigger';
GO

Поделиться через