В этой статье мы рассмотрим, как автоматизировать процесс мониторинга и отчетности по блокировкам SQL Server. Мы рассмотрим различные варианты сбора данных, выберем подходящий источник и создадим автоматизированный механизм с использованием SQL Server Agent.

Выбор источника данных

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

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

Рассмотрение типов целей: Ring Buffer vs File

Существуют два типа целей для расширенных событий: Ring Buffer и File. Цель Ring Buffer хранит данные событий в памяти во время активной сессии событий. Это означает, что когда сессия событий останавливается или SQL Server перезапускается, собранные данные теряются. С другой стороны, цель File сохраняет данные даже после остановки сессии событий или перезапуска SQL Server.

Для сохранения данных рекомендуется выбрать цель File вместо цели Ring Buffer.

Настройка расширенного события system_health

До SQL Server 2012 расширенное событие system_health имело только цель Ring Buffer, что означает, что нельзя было получить сохраненные данные. Однако, начиная с SQL Server 2012, для события system_health определены как цель Ring Buffer, так и цель File.

Если мы используем SQL Server 2008 или 2008R2, мы можем изменить сессию system_health, чтобы она также записывала данные в цель File. Это можно сделать, удалив существующую цель события и создав новую цель файла с нужными настройками.

Установка значений для переменных компонентов

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

Создание автоматизированного механизма с использованием SQL Server Agent

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

Задание SQL Server Agent будет иметь два шага: шаг “Сбор”, который вызывает процедуру Monitoring.CollectDeadlockInformation, и шаг “Преобразование/Разделение”, который вызывает процедуру Reporting.ShredDeadlockHistoryTbl. Второй шаг будет выполняться только в случае успешного выполнения первого шага.

Мы можем создать задание SQL Server Agent с помощью SQL Server Management Studio (SSMS), следуя предоставленным инструкциям. После создания задания мы можем запланировать его выполнение и настроить уведомления о сбоях задания.

Заключение

Автоматизируя процесс мониторинга и отчетности по блокировкам SQL Server, мы можем сэкономить время и обеспечить наличие ценной информации для разработчиков и службы поддержки. Регулярный сбор и отчетность позволяют более эффективно выявлять и устранять повторяющиеся проблемы с блокировками.

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

Предыдущие статьи в этой серии:

• Что такое блокировки SQL Server и как их мониторить
• Как отчитываться о возникновении блокировок SQL Server
• Как использовать расширенные события SQL Server для разбора XML блокировок и создания статистических отчетов

Загрузки:

• Runnable.SetupMonitoringAgentJob.sql
• Runnable.SetupMonitoringExtendedEvent.sql
• Все скрипты, использованные в этой серии