Расширенное устранение неполадок с помощью расширенных событий

SELECT
  OBJECT_NAME (ips.[object_id]) AS 'Object Name',
  si.name AS 'Index Name',
  ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',
  ips.page_count AS 'Pages',
  ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density'
FROM sys.dm_db_index_physical_stats (
  DB_ID ('SQLskillsDB'), NULL, NULL, NULL, 'DETAILED') ips
CROSS APPLY sys.indexes si
WHERE
  si.object_id = ips.object_id
  AND si.index_id = ips.index_id
  AND ips.index_level = 0 -- only the leaf level
  AND ips.avg_fragmentation_in_percent > 10; -- filter on fragmentation
GO

SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
  AND xo.[object_type] = 'event'
ORDER BY xp.[name];

SELECT * FROM sys.dm_xe_object_columns
  WHERE [object_name] = 'sql_statement_completed';
GO

SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
  AND xo.[object_type] = 'action'
ORDER BY xp.[name];

SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
  AND xo.[object_type] = 'target'
ORDER BY xp.[name];

Рис. 3. Отношения между модулями, пакетами и объектами расширенных событий

Когда пакет регистрируется с помощью механизма расширенны событий, все определенные им объекты становятся доступными для использования. Дополнительные сведения о пакетах и полный список терминов расширенных событий можно найти в "SQL Server Extended Events Packages ("Пакеты расширенных событий SQL Server")".

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

SELECT * FROM sys.dm_xe_sessions;
Go

Сеансы создаются, удаляются, изменяются, останавливаются и запускаются с использованием команд T-SQL. Как можно себе представить, это обеспечивает значительную гибкость, включая даже возможность динамически изменить сеанс, основываясь на программном анализе данных, записанных самим сеансом. Дополнительные сведения о сеансах см. в "SQL Server Extended Events Sessions ("Сеансы расширенных событий SQL Server")".

Соображения производительности

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

Как я объяснил выше, когда событие потребляется синхронно, код, запустивший событие, должен ждать, пока событие не потреблено. Очевидно, что если потребление события является сложным процессом, это может замедлить работу кода.

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

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

Вторая вещь, которую необходимо обдумать, состоит в том, как настроить буферизацию событий, если решено использовать асинхронные цели. Объем памяти, доступный по умолчанию для буферизации событий - 4 МБ. Задержка диспетчера между запуском события и его последующим потреблением целью, по умолчанию составляет 30 секунд. Если, скажем, необходимо предоставлять какие-то статистики события каждые 10 секунд, то необходимо подправить задержку.

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

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

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

Обычно эти параметры стоит обдумывать вместе. И здесь я не могу дать общих рекомендаций, за исключением того, что им следует уделить внимание, чтобы не столкнуться с проблемами производительности. Дополнительные сведения об этих параметрах можно найти в "CREATE EVENT SESSION (T-SQL)".

Жизнь события

После того, как сеанс расширенных событий был определен и запущен, обработка продолжается как обычно, пока отслеживаемый код не сталкивается с событием. Действия, выполняемые системой расширенных событий, обрисованы на рис. 4. Порядок их таков:

1. Выполняется проверка, чтобы увидеть наблюдает ли за событием какой-то из сеансов расширенных событий. Если нет, контроль возвращается коду, содержащему событие и обработка продолжается.
2. Определяется полезная нагрузка события и вся требуемая для нее информация собирается в памяти - другими словами, конструируется полезная нагрузка.
3. Если для события определены предикаты, они исполняются. На этом этапе, предикат может указать, что событие не следует потреблять. Если это так, контроль возвращается коду, содержащему событие и обработка продолжается.
4. Теперь система знает, что событие будет потреблено, так что она исполняет действия, связанные с событием. Теперь у события имеется завершенная полезная нагрузка и оно готово к потреблению.
5. Событие выдается синхронным целям, если таковые есть.
6. Если имеются асинхронные цели, событие буферизуется для последующей обработки.
7. Контроль возвращается коду, содержащему событие и обработка продолжается.

Рис. 4. Жизненный цикл события расширенных событий

Как я упомянул ранее, при создании сеанса события следует быть осторожным, чтобы синхронные действия или буферизация для асинхронных целей не повлияли бы на производительность отслеживаемого кода.

Использование расширенных событий

Электронная документация SQL Server 2008 включает два примера использования расширенных событий: "Практическое руководство: определение запросов, удерживающих блокировки" и "Практическое руководство: поиск объектов, на которых взято наибольшее число блокировок".

Я хотел бы разобрать пример настройки сеанса расширенных событий и проанализировать результаты. Как я узнал, когда начал использовать расширенные события в конце 2007 года, собрать простой сеанс очень просто (с помощью нехитрых операторов DDL T-SQL), но анализ результатов - нетривиальная задача.

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

Надо заметить, я был разработчиком в группе подсистемы хранилища SQL Server в течение многих лет и считаю себя высокопрофессиональным программистом C, C++ и сборок, но мне пришлось провести несколько часов, разбираясь в коде, необходимом для программного извлечения полей полезных нагрузок из данных XML. Я не пытаюсь отговорить читателей от использования расширенных событий; я просто предупреждаю, что тем, у кого нет опыта работы с данными XML, придется потратить некоторые усилия на обучение ему, прежде чем появятся результаты.

Вот мой сценарий: я администратор базы данных, использующий функцию регулятора ресурсов в SQL Server 2008, чтобы изолировать различные группы в моей компании на одном из рабочих серверов. Я создал два пула регулятора ресурсов (разработки и маркетинга) для представления использования данного сервера каждой из групп. Регулятор ресурсов позволяет мне ограничивать использование ЦП и памяти на исполнение запросов каждым пулом, но не объем используемых ими ресурсов ввода/вывода. Так что мне хотелось бы установить механизм возвратных платежей, помогающий компенсировать затраты на установку новой сети, выставляя каждой группе счет за использование ввода/вывода на этом сервере.

Я предполагаю, что простейшим способом инициализации записи информации ввода/вывода будет делать это, когда завершается работа любого оператора T-SQL и я знаю, что в пакете package0 имеется событие, именуемое sql_statement_completed. Так какие же данные собираются в полезной нагрузке события?

Исполнение следующего кода даст мне список всех данных, включая как чтения, так и записи:

SELECT [name] FROM sys.dm_xe_object_columns
  WHERE [object_name] = 'sql_statement_completed';
GO

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

Теперь мне нужно вычислить, какая группа исполнила определенный оператор T-SQL, так что нужно действие, которое сказало бы мне это. Исполнение кода дает мне список всех действий, которые я могу предпринять при запуске события включая действие, собирающее session_resource_pool_id в пакете sqlserver:

SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
   AND xo.[object_type] = 'action'
ORDER BY xp.[name];

Я могу получить список пулов ресурсов, определенный для регулятора ресурсов и соотнести его с идентификаторами, собранными моим сеансом расширенных событий. Теперь я готов определить мой сеанс. Отметьте, что когда я исполняю этот код, он сперва проверит, существует ли сеанс события с тем же именем. Если таковой будет найден, он прервет сеанс. Вот код:

IF EXISTS (
SELECT * FROM sys.server_event_sessions
    WHERE name = 'MonitorIO')
DROP EVENT SESSION MonitorIO ON SERVER;
GO

CREATE EVENT SESSION MonitorIO ON SERVER
ADD EVENT sqlserver.sql_statement_completed
  (ACTION (sqlserver.session_resource_pool_id))
ADD TARGET package0.ring_buffer;
GO

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

Для начала моего сеанса, мне нужно исполнить такой код:

ALTER EVENT SESSION MonitorIO ON SERVER
STATE = START;
GO

Теперь он работает.

После имитации некоторого объема деятельности групп маркетинга и разработки, я готов к анализу результатов сеанса. Данный код извлечет данные из кольцевого буфера:

SELECT CAST(xest.target_data AS XML) StatementData
  FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON
  xes.address = xest.event_session_address
WHERE xest.target_name = 'ring_buffer'
  AND xes.name = 'MonitorIO';
GO

Однако, он извлекает данные как одно большое значение XML. Если мне нужно разбить его на составляющие, я могу использовать код, показанный на рис. 5.

 Рис. 5. Разбиение данных XML

SELECT
  Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,
  Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,
  Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID
FROM
(SELECT CAST(xest.target_data AS XML) StatementData
  FROM sys.dm_xe_session_targets xest
  JOIN sys.dm_xe_sessions xes ON
    xes.address = xest.event_session_address
  WHERE xest.target_name = 'ring_buffer'
    AND xes.name = 'MonitorIO') Statements
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results);
GO

Это работает, но дает мне одну строку результатов на каждое записанное событие. Это не то что бы великолепный формат, кроме того, мне хотелось бы получить сводный вывод, так что я решил использовать производную таблицу, как можно увидеть на рис. 6.

Рис. 6. Получение сводного вывода

SELECT DT.ResourcePoolID,
  SUM (DT.Reads) as TotalReads,
  SUM (DT.Writes) AS TotalWrites
FROM
(SELECT
  Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,
  Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,
  Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID
FROM
(SELECT CAST(xest.target_data AS XML) StatementData
  FROM sys.dm_xe_session_targets xest
  JOIN sys.dm_xe_sessions xes ON
    xes.address = xest.event_session_address
  WHERE xest.target_name = 'ring_buffer'
    AND xes.name = 'MonitorIO') Statements
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results)) AS DT
WHERE DT.ResourcePoolID > 255 -- only show user-defined resource pools
GROUP BY DT.ResourcePoolID;
GO

Уф! Здесь явно встречается замысловатый код, но он работает хорошо. Так что теперь я получил желаемые результаты. Взгляните на результаты запроса по моим тестовым данным, показанные на рис. 7.

Я знаю, что пул ресурсов 256 относится к группе маркетинга, а 257 - к группе разработки, так что эти числа имеют смысл в плане того, насколько интенсивную работу с базой данных я ожидаю от групп в моей компании. Мне не удалось бы получить эти результаты настолько просто, если б я не использовал расширенные события.

И, наконец, мне нужно остановить сеанс, используя следующий код:

ALTER EVENT SESSION MonitorIO ON SERVER
STATE = STOP;
GO

Чтобы увидеть больше из того, о чем я говорю в плане вывода на каждой стадии данного примера, загляните в демонстрационный ролик, прилагающийся к данной статье. Его можно найти на technetmagazine.com/video.

Сеанс расширенных событий system_health

SQL Server 2008 поставляется с заранее определенным сеансом, который установлен на выполнение по умолчанию и именуется сеансом system_health. Создание этого сеанса было идеей группы поддержки продукта и он отслеживает информацию, обычно используемую ими для отладки клиентских систем, например в случае взаимоблокировки или серьезной ошибки. Этот сеанс создается и запускается как часть процесса установки для экземпляра SQL Server 2008 и он отслеживает события в кольцевом буфере, так что он не потребляет слишком много памяти.

Чтобы увидеть, что содержит кольцевой буфер, можно использовать следующий код:

SELECT CAST (xest.target_data AS XML)
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON
xes.address = xest.event_session_address
WHERE xes.name = 'system_health';
GO

Заключение

Мне сказали, что группа SQL Server планирует, в будущем, добавить в sqlserver.exe много новых событий. Фактически, число подскочило со 165 в CTP-версии февраля 2007 года (представление сообществу) до 254 в RTM-версии (окончательная первоначальная).

Стоит также обратить внимание на некоторые действительно интересно выглядящие события, например события записи данных изменений (которое я осветил в своей статье "Отслеживание изменений в корпоративной базе данных" из выпуска журнала TechNet Magazine за ноябрь 2008 года), сжатия данных и разбиения страниц индексов. Разбиение страниц индексов выглядит многообещающим способом выяснения того, какие индексы накапливают подрывающую производительность фрагментацию, без необходимости периодически выполнять DMV sys.dm_db_index_physical_stats на всех индексах.

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