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

  • Статья

Образец. Создание предупреждения агента SQL Server при помощи поставщика WMI для событий сервера

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

Пример

Прежде всего в базе данных База данных AdventureWorks2008R2 создается таблица для хранения событий графа взаимоблокировок. Таблица содержит два столбца: столбец 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 и База данных AdventureWorks2008R2.

 
USE AdventureWorks2008R2 ;
GO

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

CREATE TABLE DeadlockEvents
    (AlertTime DATETIME, DeadlockGraph XML) ;
GO
-- Add a job for the alert to run.

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

-- Add a jobstep that inserts the current time and the deadlock graph into
-- the DeadlockEvents table.

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'AdventureWorks2008R2' ;
GO

-- Set the job server for the job to the current instance of SQL Server.

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

-- Add an alert that responds to all DEADLOCK_GRAPH events for
-- the default instance. To monitor deadlocks for a different instance,
-- change MSSQLSERVER to the name of the instance.

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 AdventureWorks2008R2 ;
GO

BEGIN TRANSACTION ;
GO

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

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

USE AdventureWorks2008R2 ;
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-документ, который показывает все свойства события графа взаимоблокировок.