Настройка производительности является важным аспектом управления базой данных SQL Server. Она включает анализ планов запросов, планов выполнения, индексов, разделения и других факторов для оптимизации производительности запросов и снижения использования ресурсов. В этой статье мы рассмотрим концепцию настройки производительности с использованием расширенных событий в SQL Server.
Что такое расширенные события?
Расширенные события – это функция, введенная в SQL Server 2012, которая позволяет захватывать и анализировать события, происходящие в движке SQL Server. Она предоставляет легкий и эффективный способ мониторинга и устранения проблем с производительностью. В отличие от трассировок SQL Profiler, расширенные события имеют минимальное влияние на производительность сервера, автоматически перезапускаются после перезапуска сервера и генерируют XML-документы, которые легко анализировать.
Выявление проблем с производительностью
Существуют четыре ключевых события, которые могут помочь вам выявить запросы, требующие настройки производительности:
- Взаимоблокировки: Взаимоблокировки возникают, когда два или более процесса ожидают, пока другие процессы освободят ресурсы, что приводит к ситуации взаимной блокировки. Захватывая события взаимоблокировок с помощью расширенных событий, вы можете исследовать причину взаимоблокировок и найти способы их избежать.
- Прерывания приложения: Прерывания приложения, также известные как тайм-ауты, происходят, когда вызывающее приложение ожидает завершения запроса, но превышает максимально допустимое время. Мониторинг событий прерывания приложения позволяет выявить запросы, которые занимают слишком много времени для выполнения, и оптимизировать их производительность.
- Блокировка: Блокировка возникает, когда один процесс удерживает блокировку на ресурсе, что мешает другим процессам получить к нему доступ. Расширенные события могут захватывать события блокировки, помогая выявить корень проблем с производительностью и оптимизировать выполнение запросов.
- Долгие запросы: Долгие запросы могут значительно влиять на производительность вашего SQL Server. Мониторинг запросов, превышающих заданный порог продолжительности, позволяет выявить узкие места производительности и оптимизировать выполнение запросов.
Настройка сеансов расширенных событий
У каждого сеанса расширенных событий есть общие настройки конфигурации, которые контролируют использование ресурсов и оптимизируют скорость запросов. Эти настройки включают:
- Размер файла и количество файлов переключения: Ограничение размера файла, в котором хранятся события, и количества файлов переключения важно для обеспечения эффективного запроса данных событий.
- Потеря данных событий: Разрешение потери некоторых данных событий может снизить влияние на производительность сервера в условиях повышенного давления.
- Автоматический перезапуск: Включение автоматического перезапуска гарантирует, что сеансы расширенных событий автоматически запускаются при запуске службы SQL Server.
Примеры сеансов расширенных событий
Давайте рассмотрим несколько примеров сеансов расширенных событий для каждого из четырех событий:
Взаимоблокировки
CREATE EVENT SESSION [Deadlocks] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'Deadlocks', max_file_size=(250), max_rollover_files=(3))
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=ON, STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [Deadlocks] ON SERVER STATE = START
GO
Прерывания приложения
CREATE EVENT SESSION [TimeOuts] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_output_parameters=(1)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id)
WHERE ([result]=(2)))
ADD TARGET package0.event_file(SET filename=N'TimeOuts',max_file_size=(250),max_rollover_files=(3))
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=ON,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [TimeOuts] ON SERVER STATE = START
GO
Блокировка
CREATE EVENT SESSION [Blocking] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file(SET filename=N'Blocking',max_file_size=(250),max_rollover_files=(3))
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=ON,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [Blocking] ON SERVER STATE = START
GO
Долгие запросы
CREATE EVENT SESSION [DurationGT25Seconds] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_output_parameters=(1),collect_statement=(1)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id)
WHERE ([duration]>(25000000)))
ADD TARGET package0.event_file(SET filename=N'DurationGT25Seconds',max_file_size=(250),max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY