sys.dm_exec_query_plan (Transact-SQL)sys.dm_exec_query_plan (Transact-SQL)

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL DatabaseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database

Возвращает события инструкции Showplan в XML-формате для пакета, указанного в дескрипторе плана.Returns the Showplan in XML format for the batch specified by the plan handle. План, указанный в дескрипторе плана может быть кэширован или выполняться в данный момент.The plan specified by the plan handle can either be cached or currently executing.

Схема XML для инструкции Showplan опубликована и доступна на веб-сайте Майкрософт.The XML schema for the Showplan is published and available at this Microsoft Web site. Эта схема также доступна в папке установки SQL ServerSQL Server.It is also available in the directory where SQL ServerSQL Server is installed.

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

СинтаксисSyntax

sys.dm_exec_query_plan(plan_handle)  

АргументыArguments

plan_handleplan_handle
Токен, однозначно определяющий план выполнения запроса для пакета, который был выполнен, а его план находится в кэше планов или в данный момент выполняется.Is a token that uniquely identifies a query execution plan for a batch that has executed and its plan resides in the plan cache, or is currently executing. plan_handle имеет тип varbinary (64).plan_handle is varbinary(64).

Plan_handle можно получить из следующих объектов DMO:The plan_handle can be obtained from the following dynamic management objects:

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

Имя столбцаColumn name Тип данныхData type ОписаниеDescription
DBIDdbid smallintsmallint Идентификатор базы данных, в контексте которой выполнялась компиляция инструкции Transact-SQLTransact-SQL, соответствующей данному плану.ID of the context database that was in effect when the Transact-SQLTransact-SQL statement corresponding to this plan was compiled. Для нерегламентированных и подготовленных инструкций SQL это идентификатор базы данных, в которой происходила компиляция инструкции.For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

Столбец может содержать значение NULL.Column is nullable.
ИДobjectid intint Идентификатор объекта (например хранимой процедуры или определяемой пользователем функции) для этого плана запроса.ID of the object (for example, stored procedure or user-defined function) for this query plan. Для нерегламентированных и подготовленных пакетов этот столбец содержит значение NULL.For ad hoc and prepared batches, this column is null.

Столбец может содержать значение NULL.Column is nullable.
numbernumber smallintsmallint Целое число нумерованных хранимых процедур.Numbered stored procedure integer. Например, группа процедур для приложения orders может иметь имена вида orderproc;1, orderproc;2 и так далее.For example, a group of procedures for the orders application may be named orderproc;1, orderproc;2, and so on. Для нерегламентированных и подготовленных пакетов этот столбец содержит значение NULL.For ad hoc and prepared batches, this column is null.

Столбец может содержать значение NULL.Column is nullable.
Шифрованиеencrypted bitbit Указывает, зашифрована ли соответствующая хранимая процедура.Indicates whether the corresponding stored procedure is encrypted.

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

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

Столбец не может содержать значение NULL.Column is not nullable.
query_planquery_plan xmlxml Содержит представление Showplan времени компиляции для плана выполнения запроса, указанного в plan_handle.Contains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. Представление Showplan имеет формат XML.The Showplan is in XML format. Для каждого пакета, содержащего, например нерегламентированные инструкции языка Transact-SQLTransact-SQL, вызовы хранимых процедур и вызовы определяемых пользователем функций, формируется один план.One plan is generated for each batch that contains, for example ad hoc Transact-SQLTransact-SQL statements, stored procedure calls, and user-defined function calls.

Столбец может содержать значение NULL.Column is nullable.

ПримечанияRemarks

При следующих условиях вывод инструкции Showplan не возвращается в столбец query_plan возвращаемой таблицы для функции sys.dm_exec_query_plan.Under the following conditions, no Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_query_plan:

  • Если план запроса, указанный с помощью plan_handle , был исключен из кэша планов, то query_plan столбец возвращаемой таблицы имеет значение null.If the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. Например, такое условие может возникнуть при наличии задержки между принятием и использованием дескриптора плана функцией sys.dm_exec_query_plan.For example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_plan.

  • Некоторые инструкции Transact-SQLTransact-SQL не кэшируются, к ним относятся инструкции массовых операций, а также инструкции, содержащие строковые литералы размером более 8 КБ.Some Transact-SQLTransact-SQL statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 KB in size. Для таких инструкций нельзя получить представление Showplan в формате XML, используя функцию sys.dm_exec_query_plan, если пакет не выполняется в данный момент, потому что они не существуют в кэше.XML Showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.

  • Если Transact-SQLTransact-SQL пакет или хранимая процедура содержит вызов пользовательской функции или вызов динамического SQL, например с помощью exec (String), скомпилированный XML Showplan для определяемой пользователем функции не включается в таблицу, возвращенную sys. dm_exec_query_plan для пакета или хранимой процедуры.If a Transact-SQLTransact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. Вместо этого необходимо выполнить отдельный вызов sys. dm_exec_query_plan для маркера плана, соответствующего определяемой пользователем функции.Instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.

Если нерегламентированный запрос использует простую или принудительную параметризацию, столбец query_plan будет содержать только текст инструкции, а не фактический план запроса.When an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. Чтобы вернуть план запроса, вызовите функцию sys.dm_exec_query_plan для дескриптора плана подготовленного параметризированного запроса.To return the query plan, call sys.dm_exec_query_plan for the plan handle of the prepared parameterized query. Можно определить параметризацию запроса посредством ссылки на столбец sql представления sys.syscacheobjects или текстовый столбец динамического административного представления sys.dm_exec_sql_text.You can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects view or the text column of the sys.dm_exec_sql_text dynamic management view.

Примечание

Из-за ограничения количества вложенных уровней, разрешенных в типе данных XML , sys. dm_exec_query_plan не может возвращать планы запросов, которые соответствуют или превышают 128 уровней вложенных элементов.Due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_plan cannot return query plans that meet or exceed 128 levels of nested elements. В более ранних версиях SQL ServerSQL Server это условие предназначалось для предотвращения возврата плана запроса и формирования ошибки 6335.In earlier versions of SQL ServerSQL Server, this condition prevented the query plan from returning and generates error 6335. В SQL Server 2005 (9.x)SQL Server 2005 (9.x) пакетах обновления 2 (SP2) и более поздних версиях столбец query_plan возвращает значение null.In SQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 2 and later versions, the query_plan column returns NULL.
Для возврата выходных данных плана запроса в текстовом формате можно использовать функцию динамического управления )Transact-SQL (инструкции sys. dm_exec_text_query_plan .You can use the sys.dm_exec_text_query_plan (Transact-SQL) dynamic management function to return the output of the query plan in text format.

РазрешенияPermissions

Для выполнения sys. dm_exec_query_planпользователь должен быть членом предопределенной роли сервера sysadmin или иметь VIEW SERVER STATE разрешение на сервере.To execute sys.dm_exec_query_plan, a user must be a member of the sysadmin fixed server role or have the VIEW SERVER STATE permission on the server.

ПримерыExamples

В следующих примерах показано использование динамического административного представления sys.dm_exec_query_plan.The following examples show how to use the sys.dm_exec_query_plan dynamic management view.

Чтобы просмотреть представление Showplan в формате XML, необходимо выполнить следующие запросы в редакторе запросов среды SQL Server Management StudioSQL Server Management Studio, а затем щелкнуть элемент ShowPlanXML в столбце query_plan таблицы, возвращаемой функцией sys.dm_exec_query_plan.To view the XML Showplans, execute the following queries in the Query Editor of SQL Server Management StudioSQL Server Management Studio, then click ShowPlanXML in the query_plan column of the table returned by sys.dm_exec_query_plan. Представление Showplan в формате XML отображается на сводной панели среды Среда Management StudioManagement Studio.The XML Showplan displays in the Среда Management StudioManagement Studio summary pane. Чтобы сохранить XML Showplan в файл, щелкните правой кнопкой мыши ShowPlanXML в столбце query_plan , выберите команду сохранить результаты как, назовите файл в формате <file_name> . sqlplan, например миксмлшовплан. sqlplan.To save the XML Showplan to a file, right-click ShowPlanXML in the query_plan column, click Save Results As, name the file in the format <file_name>.sqlplan; for example, MyXMLShowplan.sqlplan.

A.A. Получение кэшированного плана запроса для медленно выполняемого запроса или пакета Transact-SQLRetrieve the cached query plan for a slow-running Transact-SQL query or batch

Планы запросов для различных типов пакетов Transact-SQLTransact-SQL, в том числе нерегламентированных пакетов, хранимых процедур и определяемых пользователем функций, кэшируются в области памяти, называемой кэшем планов.Query plans for various types of Transact-SQLTransact-SQL batches, such as ad hoc batches, stored procedures, and user-defined functions, are cached in an area of memory called the plan cache. Каждый кэшированный план запроса идентифицируется при помощи уникального идентификатора, дескриптора плана.Each cached query plan is identified by a unique identifier called a plan handle. Чтобы получить план выполнения для определенного запроса или пакета Transact-SQLTransact-SQL, можно указать дескриптор плана при помощи динамического административного представления sys.dm_exec_query_plan.You can specify this plan handle with the sys.dm_exec_query_plan dynamic management view to retrieve the execution plan for a particular Transact-SQLTransact-SQL query or batch.

Если запрос или пакет Transact-SQLTransact-SQL выполняется длительное время при определенном соединении с SQL ServerSQL Server, то для определения причины задержки необходимо получить план выполнения для этого запроса или пакета.If a Transact-SQLTransact-SQL query or batch runs a long time on a particular connection to SQL ServerSQL Server, retrieve the execution plan for that query or batch to discover what is causing the delay. В следующем примере показано, как получить представление Showplan в формате XML для медленно выполняемого запроса или пакета.The following example shows how to retrieve the XML Showplan for a slow-running query or batch.

Примечание

Чтобы выполнить этот пример, замените значения session_id и plan_handle значениями, характерными для сервера.To run this example, replace the values for session_id and plan_handle with values specific to your server.

Сначала получите идентификатор серверного процесса (SPID) для процесса, выполняющего запрос или пакет, при помощи хранимой процедуры sp_who:First, retrieve the server process ID (SPID) for the process that is executing the query or batch by using the sp_who stored procedure:

USE master;  
GO  
exec sp_who;  
GO  

Результирующий набор, возвращаемый процедурой sp_who, показывает, что идентификатор SPID равен 54.The result set that is returned by sp_who indicates that the SPID is 54. Идентификатор SPID можно использовать с динамическим административным представлением sys.dm_exec_requests для получения дескриптора плана при помощи следующего запроса:You can use the SPID with the sys.dm_exec_requests dynamic management view to retrieve the plan handle by using the following query:

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, как показано ниже.The table that is returned by sys.dm_exec_requests indicates that the plan handle for the slow-running query or batch is 0x06000100A27E7C1FA821B10600, which you can specify as the plan_handle argument with sys.dm_exec_query_plan to retrieve the execution plan in XML format as follows. План выполнения в формате XML для медленно выполняемых запросов или пакетов содержится в столбце query_plan таблицы, возвращаемой функцией sys.dm_exec_query_plan.The execution plan in XML format for the slow-running query or batch is contained in the query_plan column of the table returned by sys.dm_exec_query_plan.

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

Б.B. Получение плана каждого запроса из кэша плановRetrieve every query plan from the plan cache

Чтобы получить моментальный снимок всех планов запроса, хранимых в кэше планов, необходимо получить дескрипторы планов для всех запросов, хранящихся в кэше, запросив динамическое административное представление sys.dm_exec_cached_plans.To retrieve a snapshot of all query plans residing in the plan cache, retrieve the plan handles of all query plans in the cache by querying the sys.dm_exec_cached_plans dynamic management view. Дескрипторы планов хранятся в столбце plan_handle представления sys.dm_exec_cached_plans.The plan handles are stored in the plan_handle column of sys.dm_exec_cached_plans. Затем воспользуйтесь оператором CROSS APPLY для передачи дескрипторов плана в функцию sys.dm_exec_query_plan, как показано ниже.Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_query_plan as follows. Вывод инструкции Showplan в формате XML для каждого плана, находящегося в кэше планов, находится в столбце query_plan возвращаемой таблицы.The XML Showplan output for each plan currently in the plan cache is in the query_plan column of the table that is returned.

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

В.C. Получение всех планов запросов, для которых сервер собрал статистику запросов из кэша плановRetrieve every query plan for which the server has gathered query statistics from the plan cache

Чтобы получить моментальный снимок всех планов запроса, для которых сервером была собрана статистика и которые в настоящий момент находятся в кэше планов, необходимо получить дескрипторы планов в кэше, запросив динамическое административное представление sys.dm_exec_query_stats.To retrieve a snapshot of all query plans for which the server has gathered statistics that currently reside in the plan cache, retrieve the plan handles of these plans in the cache by querying the sys.dm_exec_query_stats dynamic management view. Дескрипторы планов хранятся в столбце plan_handle представления sys.dm_exec_query_stats.The plan handles are stored in the plan_handle column of sys.dm_exec_query_stats. Затем воспользуйтесь оператором CROSS APPLY для передачи дескрипторов плана в функцию sys.dm_exec_query_plan, как показано ниже.Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_query_plan as follows. Вывод инструкции Showplan в формате XML для каждого плана, который находится в кэше планов и для которого сервер собирал статистику, находится в столбце query_plan возвращаемой таблицы.The XML Showplan output for each plan for which the server has gathered statistics currently in the plan cache is in the query_plan column of the table that is returned.

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

Г.D. Получение сведений о первых пяти запросах по среднему времени ЦПRetrieve information about the top five queries by average CPU time

Следующий пример возвращает планы и среднее время ЦП для пяти первых запросов.The following example returns the plans and average CPU time for the top five queries.

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  

См. такжеSee Also

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