WMI 이벤트 공급자를 사용하는 일반적인 방법 중 하나는 특정 이벤트에 응답하는 SQL Server 에이전트 경고를 만드는 것입니다. 다음 샘플에서는 나중에 분석할 수 있도록 XML 교착 상태 그래프 이벤트를 테이블에 저장하는 간단한 경고를 제공합니다. SQL Server 에이전트는 WQL 요청을 제출하고, WMI 이벤트를 수신하고, 이벤트에 대한 응답으로 작업을 실행합니다. 여러 Service Broker 개체가 알림 메시지 처리에 관련되어 있지만 WMI 이벤트 공급자는 이러한 개체를 만들고 관리하는 세부 정보를 처리합니다.
예시
먼저 데이터베이스에 AdventureWorks 교착 상태 그래프 이벤트를 저장할 테이블이 만들어집니다. 테이블에는 두 개의 열이 있습니다. 이 열에는 AlertTime 경고가 실행되는 시간이 포함되고 DeadlockGraph 열에는 교착 상태 그래프가 포함된 XML 문서가 포함됩니다.
그런 다음 경고가 생성됩니다. 스크립트는 먼저 경고가 실행되는 작업을 만들고, 작업에 작업 단계를 추가하고, 작업을 SQL Server의 현재 인스턴스로 대상으로 지정합니다. 그런 다음 스크립트가 경고를 만듭니다.
작업 단계는 WMI 이벤트 인스턴스의 TextData 속성을 검색하고 DeadlockEvents 테이블의 DeadlockGraph 열에 해당 값을 삽입합니다. SQL Server는 문자열을 암시적으로 XML 형식으로 변환합니다. 작업 단계에서는 Transact-SQL 하위 시스템을 사용하므로 작업 단계에서 프록시를 지정하지 않습니다.
경고는 교착 상태 그래프 추적 이벤트가 기록될 때마다 작업을 실행합니다. WMI 경고의 경우 SQL Server 에이전트는 지정된 네임스페이스 및 WQL 문을 사용하여 알림 쿼리를 만듭니다. 이 경고의 경우 SQL Server 에이전트는 로컬 컴퓨터의 기본 인스턴스를 모니터링합니다. WQL 문은 기본 인스턴스의 모든 DEADLOCK_GRAPH 이벤트를 요청합니다. 경고가 모니터링하는 인스턴스를 변경하려면 경고의 인스턴스 이름을 MSSQLSERVER@wmi_namespace 대체합니다.
비고
SQL Server 에이전트가 WMI 이벤트를 수신하려면 msdb 및 AdventureWorks2012에서 Service Broker를 사용하도록 설정해야 합니다.
USE AdventureWorks ;
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'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 ;
GO
BEGIN TRANSACTION ;
GO
SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO
두 번째 쿼리 탭에서 다음 스크립트를 실행합니다. 이 스크립트는 하나의 결과 집합을 생성한 다음 차단하여 잠금을 획득하기 위해 대기합니다 Production.Product.
USE AdventureWorks ;
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 문서가 포함되어야 합니다.