sys.dm_exec_text_query_plan (Transact-SQL)

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

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

• Вывод плана запроса возвращается в текстовом формате.
• Размер вывода плана запроса не ограничен.
• Можно указать отдельные инструкции в пакете.

Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий), База данных SQL Azure.

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

Синтаксис

sys.dm_exec_text_query_plan   
(   
    plan_handle   
    , { statement_start_offset | 0 | DEFAULT }  
        , { statement_end_offset | -1 | DEFAULT }  
)  

Аргументы

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)

statement_start_offset | 0 | DEFAULT
Начальная позиция запроса, который описывает строка, в соответствующем тексте пакета или сохраняемом объекте, в байтах. statement_start_offset — int. Значение 0 обозначает начало пакета. Значение по умолчанию равно 0.

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

• sys.dm_exec_query_stats

• sys.dm_exec_requests

statement_end_offset | -1 | DEFAULT
Конечная позиция запроса, который описывает строка, в соответствующем тексте пакета или сохраняемом объекте, в байтах.

statement_start_offset — int.

Значение -1 обозначает конец пакета. По умолчанию используется значение 1.

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

Имя столбца	Тип данных	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	nvarchar(max)	Содержит представление инструкции времени компиляции Showplan для плана выполнения запроса, заданного аргументом plan_handle. Инструкция Showplan имеет текстовый формат. Для каждого пакета, содержащего, например, нерегламентированные инструкции Transact-SQL, вызовы хранимых процедур и вызовы определяемых пользователем функций, формируется один план. Столбец может содержать значение NULL.

Замечания

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

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

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

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

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

Разрешения

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

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

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

Примеры

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

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

Примечание.

Чтобы запустить этот пример, замените значения аргументов 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запроса или пакета. Следующий пример возвращает план запроса для указанного дескриптора плана и использует значения по умолчанию 0 и -1 для возвращения всех инструкций в запросе или пакете.

USE master;  
GO  
SELECT query_plan   
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);  
GO  

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

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

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

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

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

USE master;  
GO  
SELECT * FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);  
GO  

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

Следующий пример возвращает планы запросов и среднее время ЦП для пяти первых запросов. Функция sys.dm_exec_text_query_plan указывает значения по умолчанию 0 и -1 для возврата всех инструкций в пакете в плане запроса.

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_text_query_plan(qs.plan_handle, 0, -1)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

См. также

sys.dm_exec_query_plan (Transact-SQL)