sys.dm_exec_query_statistics_xml (Transact-SQL)

  • Статья

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

Возвращает план выполнения запросов для запросов в тестовом режиме. Используйте это динамическое административное представление для получения XML-файла showplan с временной статистикой.

Синтаксис

sys.dm_exec_query_statistics_xml(session_id)

Аргументы

session_id
Идентификатор сеанса, выполняющий пакет для поиска. session_id имеет небольшой размер. session_id можно получить из следующих динамических объектов управления:

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

Имя столбца Тип данных Description
session_id smallint Идентификатор сеанса. Не допускает значения NULL.
request_id int Идентификатор запроса. Не допускает значения NULL.
sql_handle varbinary(64) Токен, однозначно определяющий пакет или хранимую процедуру, частью которой является запрос. Допускает значение NULL.
plan_handle varbinary(64) Токен, однозначно определяющий план выполнения запроса для пакета, который выполняется в данный момент. Допускает значение NULL.
query_plan xml Содержит представление showplan среды выполнения плана выполнения запроса, указанного plan_handle с частичной статистикой. Представление Showplan имеет формат XML. Для каждого пакета, содержащего, например, нерегламентированные инструкции Transact-SQL, вызовы хранимых процедур и вызовы определяемых пользователем функций, формируется один план. Допускает значение NULL.

Замечания

Важно!

Владение возможным нарушением случайного доступа (AV) при выполнении хранимой процедуры мониторинга с sys.dm_exec_query_statistics_xml динамическим административным представлением значение атрибута <Showplan XML ParameterList>ParameterRuntimeValue было удалено в SQL Server 2017 (14.x) CU 26 и SQL Server 2019 (15.x) CU 12. Это значение может быть полезно при устранении неполадок с длительными хранимыми процедурами.

Начиная с SQL Server 2017 (14.x) CU 31 и SQL Server 2019 (15.x) CU 19, коллекция значения ParameterRuntimeValue ParameterList> атрибута <Showplan XML была повторно включена с включением флага трассировки 2446. Этот флаг трассировки позволяет собирать значение параметра среды выполнения за счет введения дополнительных затрат.

Предупреждение

Флаг трассировки 2446 не предназначен для непрерывного включения в рабочей среде, но только в целях устранения неполадок с ограниченным временем. Использование этого флага трассировки приведет к дополнительным и, возможно, значительным издержкам с точки зрения ресурсов ЦП и памяти, так как мы создаем фрагмент Showplan XML со сведениями о параметрах среды выполнения, независимо от того, вызывается ли динамическое административное представление sys.dm_exec_query_statistics_xml.

Примечание.

Начиная с SQL Server 2022 (16.x), База данных SQL Azure и Управляемый экземпляр SQL Azure для этого на уровне базы данных см. параметр FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION в ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Эта системная функция доступна начиная с SQL Server 2016 (13.x) с пакетом обновления 1 (SP1). См. КБ 3190871

Эта системная функция работает как в стандартной, так и в инфраструктуре статистики выполнения упрощенных запросов. Дополнительные сведения см. в разделе Инфраструктура профилирования запросов.

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

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

Из-за ограничения количества вложенных уровней, разрешенных в типе данных XML , sys.dm_exec_query_statistics_xml не может возвращать планы запросов, которые соответствуют или превышают 128 уровней вложенных элементов. В более ранних версиях SQL Server это условие не позволило возвращать план запроса и генерировать ошибку 6335. В SQL Server 2005 (9.x) с пакетом обновления 2 и более поздних версий столбец query_plan возвращает значение NULL.

Разрешения

Для SQL Server требуется VIEW SERVER STATE разрешение на сервере.
Для уровней "Премиум" Базы данных SQL требуется разрешение VIEW DATABASE STATE в базе данных. Для База данных SQL уровня "Стандартный" и "Базовый" требуется администратор сервера или учетная запись администратора Microsoft Entra.

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

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

Примеры

А. Просмотр динамического плана запросов и статистики выполнения для работающего пакета

В следующем примере запросы sys.dm_exec_requests , чтобы найти интересный запрос и скопировать его session_id из выходных данных.

SELECT * FROM sys.dm_exec_requests;  
GO

Затем, чтобы получить динамический план запроса и статистику выполнения, используйте скопированную session_id с системной функцией sys.dm_exec_query_statistics_xml.

--Run this in a different session than the session in which your query is running.
SELECT * FROM sys.dm_exec_query_statistics_xml(< copied session_id >);  
GO

Или в сочетании со всеми выполняемыми запросами.

--Run this in a different session than the session in which your query is running.
SELECT 
	eqs.query_plan, 
	er.session_id, 
	er.request_id, 
	er.database_id,
	er.start_time,
	er.[status], 
	er.wait_type,
	er.wait_resource, 
	er.last_wait_type,
	(er.cpu_time/1000) AS cpu_time_sec,
	(er.total_elapsed_time/1000)/60 AS elapsed_time_minutes,
	(er.logical_reads*8)/1024 AS logical_reads_KB,
	er.granted_query_memory,
	er.dop,
	er.row_count, 
	er.query_hash, 
	er.query_plan_hash
FROM sys.dm_exec_requests er
	CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) eqs
WHERE er.session_id <> @@spid;
GO

См. также

Флаги трассировки
Динамические административные представления и функции (Transact-SQL)
Динамические административные представления базы данных (Transact-SQL)