Поделиться через

Пример. Создание оповещения агент SQL Server с помощью поставщика WMI

  • Статья

Область применения: SQL Server

Один из общепринятых способов использования поставщика событий WMI состоит в создании предупреждений агента SQL Server, которые отвечают на конкретные события. В следующем образце представлено простое предупреждение, которое сохраняет события графа взаимоблокировок XML в таблице для последующего анализа. Агент SQL Server отправляет запрос WQL, получает события WMI и запускает задание в ответ на событие. Обратите внимание, что в обработке сообщения уведомления участвуют несколько объектов компонента Service Broker, но детальные операции создания и управления этими объектами возлагаются на поставщика событий WMI.

Пример

Прежде всего в базе данных AdventureWorks2022 создается таблица для хранения событий графа взаимоблокировок. Таблица содержит два столбца: AlertTime столбец содержит время выполнения оповещения, а DeadlockGraph столбец содержит XML-документ, содержащий граф взаимоблокировки.

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

Шаг задания извлекает TextData свойство экземпляра события WMI и вставляет это значение в DeadlockGraph столбец DeadlockEvents таблицы. SQL Server неявно преобразует строку в xml-формат. Так как шаг задания использует подсистему Transact-SQL, шаг задания не указывает прокси-сервер.

Предупреждение запускает задание каждый раз, когда регистрируется событие трассировки графа взаимоблокировок. Для предупреждения WMI агент SQL Server создает запрос уведомления с использованием пространства имен и указанной инструкции WQL. Применительно к этому предупреждению агент SQL Server наблюдает за экземпляром по умолчанию на локальном компьютере. Инструкция WQL запрашивает любое событие DEADLOCK_GRAPH в экземпляре по умолчанию. Чтобы изменить экземпляр, за которым ведется наблюдение с помощью предупреждения, измените имя экземпляра на MSSQLSERVER в @wmi_namespace для события.

Примечание.

Чтобы агент SQL Server получать события WMI, компонент Service Broker должен быть включен msdb и AdventureWorks2022.

USE AdventureWorks2022;
GO

IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
DROP TABLE DeadlockEvents;
GO

CREATE TABLE DeadlockEvents (
    AlertTime DATETIME,
    DeadlockGraph XML
);
GO

Добавьте задание для запуска оповещения.

EXEC msdb.dbo.sp_add_job @job_name = N'Capture Deadlock Graph',
    @enabled = 1,
    @description = N'Job for responding to DEADLOCK_GRAPH events';
GO

Добавьте шаг задания, который вставляет текущее время и граф взаимоблокировки в таблицу DeadlockEvents .

EXEC msdb.dbo.sp_add_jobstep @job_name = N'Capture Deadlock Graph',
    @step_name = N'Insert graph into LogEvents',
    @step_id = 1,
    @on_success_action = 1,
    @on_fail_action = 2,
    @subsystem = N'TSQL',
    @command = N'INSERT INTO DeadlockEvents
                (AlertTime, DeadlockGraph)
                VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
    @database_name = N'AdventureWorks2022';
GO

Установите сервер заданий для задания текущим экземпляром SQL Server.

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph';
GO

Добавьте оповещение, реагирующее на все DEADLOCK_GRAPH события для экземпляра по умолчанию. Чтобы отслеживать взаимоблокировки для другого экземпляра, измените MSSQLSERVER имя экземпляра.

EXEC msdb.dbo.sp_add_alert @name = N'Respond to DEADLOCK_GRAPH',
    @wmi_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
    @wmi_query = N'SELECT * FROM DEADLOCK_GRAPH',
    @job_name = 'Capture Deadlock Graph';
GO

Тестирование примера

Чтобы увидеть выполнение задания, следует искусственно вызвать взаимоблокировку. В СРЕДЕ SQL Server Management Studio откройте две вкладки SQL-запросов и подключите оба запроса к одному экземпляру. Запустите следующий скрипт в одной из вкладок запроса. Этот скрипт выдает один результирующий набор и завершает работу.

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

SELECT TOP (1) Name
FROM Production.Product WITH (XLOCK);
GO

Выполните следующий скрипт на второй вкладке запроса. Этот скрипт создает один результирующий набор, а затем блокируется, ожидая получения блокировки Production.Product.

USE AdventureWorks2022;
GO

BEGIN TRANSACTION;
GO

SELECT TOP (1) Name
FROM Production.Location WITH (XLOCK);
GO

SELECT TOP (1) Name
FROM Production.Product WITH (XLOCK);
GO

Выполните следующий скрипт на первой вкладке запроса. Этот скрипт блокируется, ожидая получения блокировки Production.Location. После короткого истечения времени ожидания SQL Server выберет этот скрипт или скрипт в примере в качестве жертвы взаимоблокировки и завершит транзакцию.

SELECT TOP(1) Name FROM Production.Location WITH (XLOCK);
GO

После того как будет искусственно вызвана взаимоблокировка, подождите некоторое время, пока агент SQL Server активирует предупреждение и запустит задание. Проанализируйте содержимое таблицы DeadlockEvents, запустив следующий скрипт:

SELECT * FROM DeadlockEvents;
GO

Столбец DeadlockGraph должен содержать XML-документ, который показывает все свойства события графа взаимоблокировок.

Связанный контент