Образец. Создание предупреждения агента SQL Server при помощи поставщика WMI для событий сервера
Типичный способ использования поставщика событий WMI — создать предупреждения агента SQL Server, соответствующие конкретным событиям. В следующем образце представлено простое предупреждение, которое сохраняет события графа взаимоблокировок XML в таблице для последующего анализа. Агент SQL Server отправляет запрос WQL, получает события WMI и запускает задание в ответ на событие. Обратите внимание, что хотя несколько объектов компонента Service Broker участвуют в обработке сообщения уведомления, поставщик событий WMI обрабатывает детали создания и управления этими объектами.
Пример
Во-первых, в базе данных AdventureWorks создается таблица для хранения событий графа взаимоблокировок. Таблица содержит два столбца: Столбец 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 для события.
.gif "Примечание") Примечание |
|---|
Чтобы агент SQL Server получал события WMI, необходимо включить Service Broker в msdb и AdventureWorks. |
USE AdventureWorks ;GOIF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULLBEGIN DROP TABLE DeadlockEvents ;END ;GOCREATE 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'AdventureWorks' ;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 AdventureWorks ;GOBEGIN TRANSACTION ;GOSELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;GO
Запустите следующий сценарий на второй вкладке запроса. Этот сценарий выдает один результирующий набор и блокируется, ожидая получения блокировки на Production.Product.
USE AdventureWorks ;GOBEGIN TRANSACTION ;GOSELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;GOSELECT 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-документ, который показывает все свойства события графа взаимоблокировок.