Инфраструктура профилирования запросов
Область применения:
SQL ServerAzure SQL Database
Ядро СУБД 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, и получать план запроса со статистикой времени выполнения.
Дополнительные сведения о снижении потребления ресурсов профилированием запросов см. в записи блога Developers Choice: Query progress - anytime, anywhere (Выбор разработчика: ход выполнения запроса — всегда и везде).
Заметка
Расширенные события на основе упрощенного профилирования используют данные стандартного профилирования, если инфраструктура стандартного профилирования уже включена. Допустим, имеется запущенный сеанс расширенных событий 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
Действительный план выполнения
Динамическая статистика запросов
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по