sys.dm_exec_query_plan

Статья
12/15/2008

Изменения: 12 декабря 2006 г.

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

XML-схема для инструкции Showplan опубликована на веб-узле Майкрософт. Эта схема также содержится в папке установки SQL Server 2005 в следующем каталоге.

\\Program Files\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2003\03\showplan\showplanxml.xsd

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

sys.dm_exec_query_plan ( plan_handle )

Аргументы

plan_handle
Уникальным образом определяет план запроса для пакета, который находится в кэше или выполняется в данный момент.

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

sys.dm_exec_cached_plans

sys.dm_exec_query_stats

sys.dm_exec_requests

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

Имя столбца	Тип данных	Описание
dbid	smallint	Идентификатор базы данных, в контексте которой выполнялась компиляция инструкции Transact-SQL, соответствующей данному плану. Для нерегламентированных и подготовленных пакетов этот столбец содержит значение NULL. Столбец может содержать значение NULL.
objectid	int	Идентификатор объекта (например, хранимой процедуры или пользовательской функции) для этого плана запроса. Для нерегламентированных и подготовленных пакетов этот столбец содержит значение NULL. Столбец может содержать значение NULL.
number	smallint	Целое число нумерованных хранимых процедур. Например, группа процедур для приложения orders может иметь имена вида 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 КБ. Для таких инструкций нельзя получить представление Showplan в формате XML, используя функцию sys.dm_exec_query_plan, если пакет не выполняется в данный момент, потому что они не существуют в кэше.
Если пакет Transact-SQL или хранимая процедура содержат вызов пользовательской функции или динамической инструкции SQL, например при помощи EXEC (string), скомпилированная инструкция Showplan в формате XML для пользовательской функции не включается в таблицу, возвращаемую функцией 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. Дополнительные сведения о параметризации см. в разделах Простая параметризация и Принудительная параметризация.

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

Разрешения

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

Примеры

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

Чтобы просмотреть представление Showplan в формате XML, необходимо выполнить следующие запросы в редакторе запросов среды SQL Server Management Studio, а затем щелкнуть элемент ShowPlanXML в столбце query_plan таблицы, возвращаемой функцией sys.dm_exec_query_plan. Представление Showplan в формате XML отображается в сводной области среды Management Studio. Чтобы сохранить в файле представление Showplan в формате XML, щелкните правой кнопкой мыши элемент ShowPlanXML в столбце query_plan, выберите команду Сохранить результаты как и дайте файлу имя в формате <имя_файла>.sqlplan, например MyXMLShowplan.sqlplan.

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

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

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

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

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

USE master;
GO
exec sp_who;
GO

Результирующий набор, возвращаемый процедурой sp_who, показывает, что идентификатор SPID равен 54. Идентификатор SPID можно использовать с динамическим административным представлением 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

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

Чтобы получить моментальный снимок всех планов запроса, хранимых в кэше планов, необходимо получить дескрипторы планов для всех запросов, хранящихся в кэше, запросив динамическое административное представление 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 cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO

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

Чтобы получить моментальный снимок всех планов запроса, для которых сервером была собрана статистика и которые в настоящий момент находятся в кэше планов, необходимо получить дескрипторы планов в кэше, запросив динамическое административное представление 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 qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);
GO

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

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

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

См. также

Журнал изменений

Версия	Журнал
12 декабря 2006 г.	Новое содержимое добавлено описание поведения этой функции в случае, когда план запроса содержит 128 или более уровней плана; добавлены сведения о возврате плана запроса для параметризованных нерегламентированных запросов. Добавлен пример Г. Измененное содержимое в раздел внесены разъяснения того, что план запроса может находиться в кэше или выполняться в данный момент; изменены определения dbid, objectid и number для указания того, что в столбцах возвращены значения NULL для нерегламентированных и подготовленных пакетов.
5 декабря 2005 г.	Измененное содержимое изменено место установки XML-схемы Showplan.

12 декабря 2006 г.

Новое содержимое

добавлено описание поведения этой функции в случае, когда план запроса содержит 128 или более уровней плана;
добавлены сведения о возврате плана запроса для параметризованных нерегламентированных запросов.
Добавлен пример Г.

Измененное содержимое

в раздел внесены разъяснения того, что план запроса может находиться в кэше или выполняться в данный момент;
изменены определения dbid, objectid и number для указания того, что в столбцах возвращены значения NULL для нерегламентированных и подготовленных пакетов.

5 декабря 2005 г.