sys.dm_exec_query_plan (Transact-SQL)

Применимо к:SQL ServerБаза данных Azure SQLУправляемый экземпляр Azure SQLБаза данных SQL в Microsoft Fabric

Возвращает события инструкции Showplan в XML-формате для пакета, указанного в дескрипторе плана. План, указанный в дескрипторе плана может быть кэширован или выполняться в данный момент.

Схема XML для Showplan публикуется и доступна на этом веб-сайте Майкрософт. Он также доступен в каталоге, в котором установлен SQL Server.

Соглашения о синтаксисе Transact-SQL

Syntax

sys.dm_exec_query_plan(plan_handle)  

Аргументы

plan_handle
Это токен, который уникально идентифицирует план выполнения запросов для выполненного пакета, план которого хранится в кэше планов или пребывает на этапе выполнения. plan_handle — varbinary(64).

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

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

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

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

Замечания

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

• Если план запроса, определенный использованием аргумента plan_handle, извлекается из кэша планов, столбец query_plan возвращаемой таблицы имеет значение NULL. Например, это условие может возникать, если существует задержка времени между моментом захвата дескриптора плана и когда он использовался с sys.dm_exec_query_plan.

• Некоторые инструкции Transact-SQL не кэшируются. К ним относятся инструкции массовых операций, а также инструкции, содержащие строковые литералы размером более 8 КБ. Xml Showplans для таких инструкций нельзя получить с помощью sys.dm_exec_query_plan , если пакет в настоящее время не выполняется, так как они не существуют в кэше.

• Если пакет Transact-SQL или хранимая процедура содержит вызов определяемой пользователем функции или вызов динамического SQL, например с помощью EXEC (string), скомпилированный XML Showplan для определяемой пользователем функции не включается в таблицу, возвращаемую sys.dm_exec_query_plan для пакетной или хранимой процедуры. Вместо этого необходимо выполнить отдельный вызов sys.dm_exec_query_plan для дескриптора плана, соответствующего определяемой пользователем функции.

Если в нерегламентированном запросе используется простая или принудительной параметризация, столбец query_plan будет содержать только текст инструкции, а не фактический план запроса. Чтобы вернуть план запроса, вызовите sys.dm_exec_query_plan для дескриптора плана подготовленного параметризованного запроса. Вы можете определить, был ли запрос параметризован путем ссылки на столбец SQL представления sys.syscacheobjects или текстовый столбец sys.dm_exec_sql_text динамического административного представления.

Note

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

Permissions

Чтобы выполнить sys.dm_exec_query_plan, пользователь должен быть членом предопределенных ролей сервера sysadmin или иметь VIEW SERVER STATE разрешение на сервере.

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

Требуется VIEW разрешение на состояние производительности сервера на сервере.

Examples

В следующих примерах показано, как использовать динамическое представление управления sys.dm_exec_query_plan .

Чтобы просмотреть XML-шоупланы, выполните следующие запросы в Редактор запросов SQL Server Management Studio, а затем щелкните ShowPlanXML в столбце query_plan таблицы, возвращаемой sys.dm_exec_query_plan. Xml Showplan отображается в области сводки Management Studio. Чтобы сохранить XML Showplan в файл, щелкните правой кнопкой мыши ShowPlanXML в столбце query_plan, нажмите кнопку

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

Планы запросов для различных типов пакетов Transact-SQL, таких как нерегламентированные пакеты, хранимые процедуры и определяемые пользователем функции, кэшируются в области памяти, называемой кэшем планов. Каждый кэшированный план запроса идентифицируется при помощи уникального идентификатора, дескриптора плана. Этот дескриптор плана можно указать с помощью динамического представления управления sys.dm_exec_query_plan , чтобы получить план выполнения для конкретного запроса Transact-SQL или пакета.

Если запрос или пакет Transact-SQL выполняется длительное время при определенном типе подключения к SQL Server, то для определения причины задержки необходимо получить план выполнения для этого запроса или пакета. В следующем примере показано, как получить представление Showplan в формате XML для медленно выполняемого запроса или пакета.

Note

Чтобы запустить этот пример, замените значения аргументов session_id и plan_handle на значения, соответствующие вашему серверу.

Сначала получите идентификатор сессии (SPID) процесса, выполняющего запрос или пакет, используя сохранённую sp_who процедуру:

USE master;  
GO  
exec sp_who;  
GO  

Набор результатов, возвращаемый , sp_who указывает, что идентификатор сессии — это 54. Вы можете использовать идентификатор сессии с режимом sys.dm_exec_requests динамического управления для получения дескриптора плана, используя следующий запрос:

USE master;  
GO  
SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO  

Таблица, возвращаемая sys.dm_exec_requests , указывает, что дескриптор плана для медленно выполняющегося запроса или пакета 0x06000100A27E7C1FA821B10600, который можно указать в качестве аргумента plan_handle , sys.dm_exec_query_plan чтобы получить план выполнения в формате XML, как показано ниже. План выполнения в формате XML для медленно выполняющегося запроса или пакета содержится в столбце query_plan таблицы, возвращаемой sys.dm_exec_query_plan.

USE master;  
GO  
SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO  

B. Получение плана каждого запроса из кэша планов

Чтобы получить моментальный снимок всех планов запроса, хранимых в кэше планов, необходимо получить дескрипторы планов для всех запросов, хранящихся в кэше, запросив динамическое административное представление sys.dm_exec_cached_plans. Дескрипторы планов хранятся в столбце plan_handle представления sys.dm_exec_cached_plans. Затем воспользуйтесь оператором CROSS APPLY для передачи дескрипторов плана в функцию sys.dm_exec_query_plan, как показано ниже. Вывод инструкции Showplan в формате XML для каждого плана, находящегося в кэше планов, находится в столбце query_plan возвращаемой таблицы.

USE master;  
GO  
SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO  

C. Получение всех планов запросов, для которых сервер собрал статистику запросов из кэша планов

Чтобы получить моментальный снимок всех планов запроса, для которых сервером была собрана статистика и которые в настоящий момент находятся в кэше планов, необходимо получить дескрипторы планов в кэше, запросив динамическое административное представление sys.dm_exec_query_stats. Дескрипторы планов хранятся в столбце plan_handle представления sys.dm_exec_query_stats. Затем воспользуйтесь оператором CROSS APPLY для передачи дескрипторов плана в функцию sys.dm_exec_query_plan, как показано ниже. Вывод инструкции Showplan в формате XML для каждого плана, который находится в кэше планов и для которого сервер собирал статистику, находится в столбце query_plan возвращаемой таблицы.

USE master;  
GO  
SELECT * 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D. Получение сведений о первых пяти запросах по среднему времени ЦП

Следующий пример возвращает планы и среднее время ЦП для пяти первых запросов.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
   plan_handle, query_plan   
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

Связанные материалы

• Представления и функции управления системой динамики
• sys.dm_exec_cached_plans (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• sys.dm_exec_requests (Transact-SQL)
• sys.sp_who (Transact-SQL)
• Справочник по операторам логического и физического плана выполнения
• sys.dm_exec_text_query_plan (Transact-SQL)