Extended Events

• Extended Events
  • Зачем
  • Плюсы и минусы
  • Паттерны
    • CPU
    • Long Query поиск тяжелых запросов
    • Locks
      • Lock Escalation
    • Errors
    • TempDB
    • Разбор логов

Зачем

Содержат информацию о запросах с параметрами за определенный период с затратами на запрос по CPU, IOPS, длительности, кол-ву записей, пользователе, logical\physical reads в БД.

Microsoft SQL Server:

• 2008: первая версия ХЕ без GUI
• 2012: профайлер признан устаревшим
• 2014: хорошее рабочее решение
• 2019: фантастический инструмент, включающий более 1300 событий

Плюсы и минусы

• Значительно меньшее влияние на производительность при включенном сборе данных, причем имеются расширенные настройки, с помощью которых на это можно влиять.
• Настройки сбора данных (события, фильтры и др.) можно менять на активных сессиях, прямо во время сбора данных.
• Доступен хэш запросов, чтобы идентифицировать одинаковые тексты запросов.
• Можно настроить различные способы хранения логов, причем одновременно в нескольких вариантах.
• Встроенные инструменты в SQL Server Managment Studio и инструкции TSQL для работы с ними.

Паттерны

Long Query поиск тяжелых запросов

CPU

• событий по CPU достаточно использовать два события
  • RPC:Completed - вызов процедур через обращение к sp_executesql
  • SQL:BatchCompleted - выполнение пакета запросов
• сортировка по cpu_time
• на сервере может быть включен параллелизм, тогда затраченное процессорное время не будет равно времени выполнения запроса даже приблизительно
• фильтр по полю времени выполнения события (поле “duration”) в микросекундах, которые выполняются более 5 секунд (5 000 000 микросекунд)

RAM

• используется объем логических чтений, т.е. тех, которые были выполнены из оперативной памяти. Это важно, т.к. SQL Server кэширует страницы, полученные с диска в буферный кэш, то есть в оперативную память. Именно поэтому SQL Server такой прожорливый в части использования RAM
• фильтр на поле с размером прочитанных данных, те logical reads
  • зависит от конкретной системы и размера базы. Обычно использую значение фильтра от 10000 до 50000 прочитанных страниц (то есть от ~80 МБ до ~400 МБ, т.к. 1 страница = 8 КБ)
• Сбор тяжелых запросов по логическим чтениям позволит определить те запросы, которые чаще всего используют буферный кэш, тем самым “вымывая” из него данные для других запросов

Locks

• собирать информацию об ожиданиях на блокировках с помощью механизма отчетов по заблокированным процессам. Он включается в свойствах сервера и выполняет сбор статистики с заданной периодичностью (обычно 5 секунд достаточно)
• Deadlock
  • в разрезе БД, таблиц, запросов
• Blocked Process Report
• Deadlock XML Report

Lock Escalation

CREATE EVENT SESSION [TMP_LockEscalation] ON SERVER ADD EVENT sqlserver.lock_escalation( ACTION(sqlserver.context_info, sqlserver.database_name, sqlserver.is_system, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) ) ADD TARGET package.event_file(SET filename=N’TMP_LockEscalation’) 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=ON)

Errors

• отслеживать любые ошибки, происходящие на уровне СУБД. Для этого воспользуемся скриптом для сбора всех ошибок с помощью события SQL:ErrorReported
• парсинг ошибок в таблицу
• анализ ошибок

TempDB

• TempDB spiils - не хватка памяти при оценке плана выполнения запросов и использование tempdb для кэширования запросов - снижение Latency

Causality tracking Отслеживание причинно-следственной связи

Параметр отслеживания причинно-следственной связи в сеансе расширенных событий SQL Server помогает устранить проблемы с производительностью. Важно понимать порядок событий для транзакции в SQL Server. Кроме того, вы можете использовать его для отслеживания выполнения отдельных хранимых процедур или инструкций (продолжительности) из вложенной хранимой процедуры

Разбор логов

• копируем файлы XEL с собранными данными на сервер логов или локальный компьютер, т.к. выполнять анализ данных на рабочем сервере дело рисковое, ведь он может “съесть” значительную часть ресурсов.
• запустим SQL Server Managment Studio и перейдем в “Файл -> Открыть -> Объединить файлы расширенных событий….”.
  • После добавляем в список все файлы, которые хотим обработать
  • запускаем мастер выгрузки событий в таблицу базы данных