Инфраструктура профилирования запросов

Статья
01/02/2025

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

Ядро SQL Server предоставляет возможность доступа к информации о времени выполнения о планах выполнения запросов. При возникновении проблемы с производительностью одним из самых важных действий является получение сведений о том, какая рабочая нагрузка выполняется в данный момент и каким образом происходит управление ресурсами. Для осуществления этого важно иметь доступ к действительному плану выполнения.

Несмотря на то, что для доступности действительного плана запроса необходимо дождаться завершения выполнения запроса, динамическая статистика запросов может анализировать процесс выполнения запроса в режиме реального времени, по мере передачи управления от одного оператора плана запроса другому. План динамического запроса отображает общую статистику выполнения запроса и статистику выполнения на уровне оператора, например количество созданных строк, время ожидания, ход выполнения оператора и т. д. Так как эти данные доступны в режиме реального времени без необходимости ожидать завершения запроса, эти статистические данные выполнения чрезвычайно полезны для отладки проблем с производительностью запросов, таких как длительные запросы, и запросы, которые выполняются бесконечно и никогда не завершаются.

Стандартная инфраструктура профилирования статистики выполнения запросов

Инфраструктуру профилей статистики выполнения запросов, или стандартное профилирование, необходимо включить для сбора сведений о планах выполнения, а именно числе строк, использовании ЦП и операциях ввода-вывода. Следующие методы сбора сведений о плане выполнения для целевого сеанса используют стандартную инфраструктуру профилирования:

Примечание.

Щелкнув кнопку Включить статистику динамических запросов в SQL Server Management Studio, вы используете стандартную инфраструктуру профилирования.
В более поздних версиях SQL Server, если включена упрощенная инфраструктура профилирования, она используется статистикой динамических запросов вместо стандартного профилирования при просмотре с помощью монитора действий или непосредственного запроса sys.dm_exec_query_profiles dmV.

Следующие методы сбора сведений о плане выполнения глобально для всех сеансов используют стандартную инфраструктуру профилирования:

Расширенное событие query_post_execution_showplan. Сведения о включении расширенных событий см. в статье Monitor System Activity Using Extended Events.
Событие трассировки Showplan XML в SQL Trace и SQL Server Profiler. Дополнительные сведения об этом событии трассировки см. в Showplan XML Event Class.

При запуске сеанса расширенного события, которое использует событие query_post_execution_showplan, также заполняется динамическое административное представление sys.dm_exec_query_profiles, которое включает возможности динамической статистики запросов для всех сеансов, используя Монитор Активности или непосредственно запрашивая динамическое административное представление. Дополнительные сведения см. в статье Live Query Statistics.

Инфраструктура профилирования статистики выполнения упрощенного запроса

Начиная с SQL Server 2014 (12.x) SP2 и SQL Server 2016 (13.x), была представлена новая облегченная инфраструктура профилирования статистики выполнения запросов, или упрощенное профилирование.

Примечание.

Хранимые процедуры, скомпилированные в собственном коде, не поддерживаются в упрощенном профилировании.

Упрощенная инфраструктура профилирования статистики выполнения запросов версии 1

Область применения: SQL Server (SQL Server 2014 (12.x) с пакетом обновления 2 (SP2) до SQL Server 2016 (13.x)).

Начиная с SQL Server 2014 (12.x) с пакетом обновления 2 (SP2) и SQL Server 2016 (13.x), затраты на производительность для сбора сведений о планах выполнения были сокращены с введением упрощенного профилирования. В отличие от стандартного профилирования, облегченное профилирование не собирает информацию о времени выполнения ЦП. Однако упрощенное профилирование по-прежнему собирает сведения о количестве строк и сведения об использовании операций ввода-вывода.

Также было добавлено новое расширенное событие query_thread_profile, использующее упрощенное профилирование. Это расширенное событие предоставляет статистику выполнения по операторам, позволяя получить больше сведений о производительности каждого узла и потока. Ниже приведен пример сеанса, использующего это расширенное событие.

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Примечание.

Дополнительные сведения о снижении потребления ресурсов профилированием запросов см. в записи блога Developers Choice: Query progress - anytime, anywhere (Выбор разработчика: ход выполнения запроса — всегда и везде).

При запуске сеанса расширенного события с использованием события query_thread_profile, динамическое административное представление sys.dm_exec_query_profiles также заполняется с помощью упрощенного профилирования, что позволяет отслеживать статистику запросов в реальном времени для всех сеансов с использованием монитора активности или прямого запроса этого представления.

Упрощенная инфраструктура профилирования статистики выполнения запросов версии 2

Область применения: SQL Server (SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) до SQL Server 2017 (14.x)).

SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) включает обновленную версию упрощенного профилирования с минимальными затратами. Упрощенное профилирование можно также включить глобально с помощью флага трассировки 7412 в версиях, указанных выше в поле Применимо к. Представлена новая функция динамического управления данными sys.dm_exec_query_statistics_xml для предоставления плана выполнения запроса для запросов, находящихся в обработке.

Начиная с SQL Server 2016 (13.x) SP2 CU3 и SQL Server 2017 (14.x) CU11, если упрощенное профилирование не включено глобально, новый аргумент указанной подсказки запроса USE HINT QUERY_PLAN_PROFILE можно использовать для включения упрощенного профилирования на уровне запроса для любой сессии. После завершения запроса, содержащего это новое указание, также выводится новое расширенное событие query_plan_profile, предоставляющее действительный план выполнения в формате XML, аналогично расширенному событию query_post_execution_showplan.

Примечание.

Расширенное событие query_plan_profile также использует упрощенное профилирование, даже если указание запроса отсутствует.

Пример сеанса с расширенным событием query_plan_profile можно настроить, как показано ниже:

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Упрощенная инфраструктура профилирования статистики выполнения запросов версии 3

Область применения: SQL Server (начиная с SQL Server 2019 (15.x)) и База данных SQL Azure

SQL Server 2019 (15.x) и База данных SQL Azure включают в себя только что измененную версию упрощенного профилирования, собирающую сведения о количестве строк для всех выполнений. Включение упрощенного профилирования производится по умолчанию в SQL Server 2019 (15.x) и Azure SQL Database. Начиная с SQL Server 2019 (15.x), флаг трассировки 7412 не действует. Упрощенное профилирование можно отключить на уровне базы данных с помощью конфигурации уровня базы данных LIGHTWEIGHT_QUERY_PROFILING: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Появилась новая функция динамического управления sys.dm_exec_query_plan_stats, которая возвращает эквивалент последнего известного действительного плана выполнения для большинства запросов и называется статистика плана последнего запроса. Статистику последнего плана запросов можно включить на уровне базы данных, используя конфигурацию LAST_QUERY_PLAN_STATS: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Новое расширенное событие query_post_execution_plan_profile служит для сбора эквивалента действительного плана выполнения на основе упрощенного, а не стандартного профилирования, как в случае с событием query_post_execution_showplan. SQL Server 2017 (14.x) также предлагает это событие, начиная с CU14. Пример сеанса с расширенным событием query_post_execution_plan_profile можно настроить, как показано ниже.

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Пример 1. Сеанс расширенных событий с использованием стандартного профилирования

CREATE EVENT SESSION [QueryPlanOld] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename = N'C:\Temp\QueryPlanStd.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

Пример 2. Сеанс расширенных событий на основе упрощенного профилирования

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPlanLWP.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

Руководство по использованию инфраструктуры профилирования запросов

В приведенной ниже таблице перечислены действия по включению стандартного или упрощенного профилирования глобально (на уровне сервера) или в одном сеансе. Также включена информация о первой версии, в которой доступно это действие.

Область	Стандартное профилирование	Легковесное профилирование
Глобальный	Сеанс XEvent с `query_post_execution_showplan` XE; Начиная с SQL Server 2012 (11.x)	Флаг трассировки 7412; Начиная с SQL Server 2016 (13.x) с пакетом обновления 1 (SP1)
Глобальный	SQL Trace и SQL Server Profiler с событием трассировки `Showplan XML`; начиная с версии SQL Server 2000	Сеанс XEvent с `query_thread_profile` XE; Начиная с SQL Server 2014 (12.x) с пакетом обновления 2 (SP2)
Глобальный	-	Сеанс XEvent с `query_post_execution_plan_profile` XE; Начиная с SQL Server 2017 (14.x) CU14 и SQL Server 2019 (15.x)
Сеанс	Используйте `SET STATISTICS XML ON`. Начиная с SQL Server 2000	`QUERY_PLAN_PROFILE` Используйте указание запроса вместе с сеансом XEvent с `query_plan_profile` XE; Начиная с SQL Server 2016 (13.x) Service Pack 2 (SP2) CU3 и SQL Server 2017 (14.x) CU11
Сеанс	Используйте `SET STATISTICS PROFILE ON`; начиная с версии SQL Server 2000	-
Сеанс	Нажмите кнопку " Статистика живых запросов" в SSMS; начиная с SQL Server 2014 (12.x) SP2	-

Замечания

Внимание

Из-за возможного нарушения доступа к памяти при выполнении хранимой процедуры мониторинга, которая ссылается на sys.dm_exec_query_statistics_xml, убедитесь, что обновление KB 4078596 установлено в SQL Server 2016 (13.x) и SQL Server 2017 (14.x).

Начиная с упрощенного профилирования версии 2, благодаря его низкому потреблению ресурсов, любой сервер, у которого нет перегрузки ЦП, может выполнять упрощенное профилирование непрерывно. Это позволяет специалистам по работе с базами данных в любое время подключаться к любому запущенному выполнению, например с помощью монитора активности или прямого запроса sys.dm_exec_query_profiles, и получать план запроса со статистикой времени выполнения.

Примечание.

Если инфраструктура стандартного профилирования уже включена, расширенные события, использующие лёгкое профилирование, будут использовать данные стандартного профилирования. Допустим, запущен сеанс расширенных событий query_post_execution_showplan, и запускается еще один сеанс событий query_post_execution_plan_profile. Во втором сеансе будут использоваться данные стандартного профилирования.

Примечание.

В SQL Server 2017 (14.x) упрощённое профилирование отключено по умолчанию, но активируется при запуске зависящей от query_post_execution_plan_profile трассировки XEvent, а затем снова деактивируется при её остановке. В результате, если на экземпляре SQL Server 2017 (14.x) часто запускаются и останавливаются трассировки Xevent на основе query_post_execution_plan_profile, настоятельно рекомендуется активировать упрощенное профилирование на глобальном уровне с помощью traceflag 7412, чтобы избежать постоянной нагрузки при активации и деактивации.

См. также

Наблюдение и настройка производительности
Средства контроля и настройки производительности
Открытие Диспетчера активности (SQL Server Management Studio)
Монитор активности
Мониторинг производительности с использованием хранилища запросов
Мониторинг активности системы с помощью расширенных событий
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Флаги трассировки
Справочник по логическим и физическим операторам Showplan
Действительный план выполнения
Динамическая статистика запросов

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

Инфраструктура профилирования запросов

Стандартная инфраструктура профилирования статистики выполнения запросов

Инфраструктура профилирования статистики выполнения упрощенного запроса

Упрощенная инфраструктура профилирования статистики выполнения запросов версии 1

Упрощенная инфраструктура профилирования статистики выполнения запросов версии 2

Упрощенная инфраструктура профилирования статистики выполнения запросов версии 3

Пример 1. Сеанс расширенных событий с использованием стандартного профилирования

Пример 2. Сеанс расширенных событий на основе упрощенного профилирования

Руководство по использованию инфраструктуры профилирования запросов

Замечания

См. также

Обратная связь

Дополнительные ресурсы