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

Когда мы собираем информацию о блокировках в формате XML, мы можем использовать функцию XQuery в Transact-SQL для запроса содержимого XML-данных непосредственно в SQL Server. Это позволяет нам создавать отчеты на основе свойств блокировок, таких как имя приложения и запросы, участвующие в блокировках.

Для обработки XML-графов блокировок мы будем следовать трехэтапному процессу:

1. Извлечение: Этот этап включает извлечение XML-графов блокировок и сохранение их в таблице. Мы подробно обсудили этот этап в предыдущей статье.
2. Преобразование/Разделение: На этом этапе мы разделяем содержимое XML на ряд столбцов, что упрощает вычисление статистики и создание отчетов.
3. Отчет: После преобразования XML-графов мы можем создавать отчеты на основе извлеченных свойств.

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

Теперь давайте ближе рассмотрим XML-графы блокировок. Каждый граф разделен на три секции:

1. Список жертв: Эта секция идентифицирует процесс, который был убит потоком мониторинга блокировок.
2. Список процессов: Эта секция перечисляет процессы, участвующие в ситуации блокировки. У каждого процесса есть различные свойства, такие как имя клиентского приложения, имя хоста и уровень изоляции транзакции.
3. Список ресурсов: Эта секция перечисляет ресурсы базы данных, используемые процессами во время ситуации блокировки.

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

Вот пример столбцов, которые мы включим в таблицу назначения:

[DeadlockID] BIGINT NOT NULL,
[isVictim] BIT NOT NULL,
[ProcessQty] INT NOT NULL,
[ProcessNbr] BIGINT NOT NULL,
[LockMode] VARCHAR(10) NOT NULL,
[LockedObject] VARCHAR(1024) NULL,
[DatabaseId] INT NULL,
[AssociatedObjectId] BIGINT NULL,
[AssociatedObjectName] VARCHAR(1024) NULL,
[LockProcess] VARCHAR(50) NOT NULL,
[ThreadId] INT NOT NULL,
[SPID] INT NOT NULL,
[SBID] INT NOT NULL,
[ECID] INT NOT NULL,
[TranCount] INT NOT NULL,
[LockEvent] VARCHAR(8000) NULL,
[LockedMode] VARCHAR(10) NULL,
[WaitProcessID] VARCHAR(200) NULL,
[WaitMode] VARCHAR(10) NULL,
[WaitResource] VARCHAR(200) NOT NULL,
[WaitType] VARCHAR(100) NULL,
[IsolationLevel] VARCHAR(200) NOT NULL,
[LogUsed] BIGINT NOT NULL,
[ClientApp] VARCHAR(512) NULL,
[HostName] VARCHAR(512) NOT NULL,
[LoginName] VARCHAR(256) NOT NULL,
[TransactionTime] DATETIME NOT NULL,
[BatchStarted] DATETIME NOT NULL,
[BatchCompleted] DATETIME NOT NULL,
[QueryStatement] VARCHAR(max) NULL,
[SQLHandle] VARCHAR(64) NULL,
[SQLHandleText] VARCHAR(max) NULL,
[InputBuffer] XML NULL,
[ExecutionStack] XML NULL

С такой структурой таблицы мы можем хранить необходимые свойства для целей отчетности.

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

Процедура будет выполнять следующие шаги:

1. Проверить существование исходной и целевой таблиц.
2. Создать временную таблицу назначения.
3. Получить список новых блокировок во временную таблицу.
4. Для каждой новой блокировки разделить XML-граф блокировки и вставить его во временную таблицу назначения.
5. Обновить временную таблицу назначения с именами связанных объектов.
6. Обновить временную таблицу назначения с текстом T-SQL, соответствующим каждому значению sql_handle.
7. Сохранить содержимое временной таблицы назначения в фактическую таблицу назначения.

Самая важная часть этого алгоритма – это шаг “Разделить XML-граф блокировки и вставить его во временную таблицу назначения”. Этот шаг включает использование общих выражений CTE и множественного программирования для эффективной обработки XML-графов и вставки данных в таблицу.

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

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