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

Статья
08/21/2023

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

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 и SQL Server Profiler. Дополнительные сведения об этом событии трассировки см. в статье Showplan XML, класс событий.

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

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

Начиная с SQL Server 2014 (12.x) с пакетом обновления 2 (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) с пакетом обновления 2 (SP2) и 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) и База данных SQL Azure. Начиная с 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" и 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) с пакетом обновления 2 (SP2) и SQL Server 2017 (14.x) CU11
Сеанс	Используйте `SET STATISTICS PROFILE ON`. Минимальная версия: SQL Server 2000	-
Сеанс	Нажмите кнопку " Статистика динамических запросов" в SSMS; Начиная с SQL Server 2014 (12.x) с пакетом обновления 2 (SP2)	-

Замечания

Внимание

Из-за возможного нарушения случайного доступа при выполнении хранимой процедуры мониторинга, ссылающейся на sys.dm_exec_query_statistics_xml, убедитесь, что 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, а затем деактивируется снова при остановке трассировки. В результате, если трассировки Xevent на основе query_post_execution_plan_profile часто запускаются и остановлены на экземпляре SQL Server 2017 (14.x), настоятельно рекомендуется активировать упрощенное профилирование на глобальном уровне с помощью traceflag 7412, чтобы избежать повторяющихся затрат на активацию и деактивацию.

См. также

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

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

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

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

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

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

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

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

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

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

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

Замечания

См. также

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

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