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

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

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

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

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

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

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Динамическая статистика запросов

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

• Расширенное событие 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);

При выполнении сеанса расширенного события, использующего событие 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 можно настроить, как показано ниже:

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);

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

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

Замечания

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

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

См. также

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