Поделиться через


sys.dm_exec_text_query_plan

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

  • Вывод плана запроса возвращается в текстовом формате.

  • Размер вывода плана запроса не ограничен.

  • Можно указать отдельные инструкции в пакете.

Значок ссылки на разделСинтаксические обозначения в 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).

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

    sys.dm_exec_cached_plans

    sys.dm_exec_query_stats

    sys.dm_exec_requests

  • 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.

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

Имя столбца

Тип данных

Описание

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

nvarchar(max)

Содержит представление инструкции Showplan времени компиляции для плана выполнения запроса, заданного аргументом plan_handle. Инструкция Showplan имеет текстовый формат. Для каждого пакета, содержащего, например нерегламентированные инструкции языка Transact-SQL, вызовы хранимых процедур и вызовы определяемых пользователем функций, формируется один план.

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

Замечания

При следующих условиях вывод инструкции Showplan не возвращается в столбец plan возвращаемой таблицы для функции sys.dm_exec_text_query_plan.

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

  • Некоторые инструкции Transact-SQL не кэшируются, к ним относятся инструкции массовых операций, а также инструкции, содержащие строковые литералы размером более 8 КБ. Для таких инструкций нельзя получить инструкцию Showplan с помощью функции 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 на сервере.

Примеры

А. Получение кэшированного плана запроса для медленно выполняемого запроса или пакета 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

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

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

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

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

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

Следующий пример возвращает планы запросов и среднее время ЦП для пяти первых запросов. Функция 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

См. также

Справочник

Основные понятия

Другие ресурсы