Skip to main content
sys.dm_exec_query_plan (Transact-SQL)
 

ОБЛАСТЬ ПРИМЕНЕНИЯ ЭТОЙ СТАТЬИ: даSQL Server (начиная с 2008) нетБаза данных SQL Azure нетХранилище данных SQL Azure нетParallel Data Warehouse

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

Схема XML для Showplan опубликована и доступна на веб-узле Microsoft. Эта схема также доступна в папке установки SQL Server.

Topic link icon  Синтаксические обозначения в Transact-SQL

  
sys.dm_exec_query_plan ( plan_handle )  

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

plan_handlevarbinary(64). plan_handle можно получить из следующих объектов DMO:

sys.dm_exec_cached_plans

sys.dm_exec_query_stats

sys.dm_exec_requests

Имя столбцаТип данныхDescription
DBIDsmallintИдентификатор базы данных, в контексте которой выполнялась компиляция инструкции Transact-SQL, соответствующей данному плану. Для нерегламентированных и подготовленных инструкций SQL это идентификатор базы данных, в которой происходила компиляция инструкции.

Столбец может содержать значение NULL.
ObjectIDintИдентификатор объекта (например хранимой процедуры или определяемой пользователем функции) для этого плана запроса. Для нерегламентированных и подготовленных пакетов этот столбец содержит null.

Столбец может содержать значение NULL.
номерsmallintЦелое число нумерованных хранимых процедур. Например, группа процедур для заказов приложения может называться orderproc;&1;, orderproc;&2;и так далее. Для нерегламентированных и подготовленных пакетов этот столбец содержит null.

Столбец может содержать значение NULL.
шифрованиебитУказывает, зашифрована ли соответствующая хранимая процедура.

0 = не зашифрована

1 = зашифрована

Столбец не может содержать значение NULL.
query_planxml;Содержит представление 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 (строка), скомпилированная инструкция 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 это условие предназначалось для предотвращения возврата плана запроса и формирования ошибки 6335. В SQL Server 2005 с пакетом обновления 2 и более поздних версиях query_plan столбец возвращает значение NULL. Можно использовать sys.dm_exec_text_query_plan (Transact-SQL) функции динамического управления для возврата плана запроса в текстовом формате.

Для выполнения 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. Чтобы сохранить XML Showplan в файл, щелкните правой кнопкой мыши ShowPlanXML в query_plan столбец, нажмите кнопку сохранить результаты как, имя файла в формате имя_файланастроек .sqlplan, например, MyXMLShowplan.sqlplan.

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

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

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

System_CAPS_ICON_note.jpg Примечание


Чтобы выполнить этот пример, замените значения 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  

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