SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

WMI イベント警告を使用して SQL Server でイベント駆動でアクションを実行する

leave a comment

SQL Server でイベント発生時の情報を取得する方法としては、拡張イベント (xEvent) があります。
拡張イベントを使用することで、SQL Server で発生する様々なイベントを取得することができます。

拡張イベントは情報を取得する際に強力な機能ですが、「イベントが発生した際に任意の DMV から情報を取得する」ということができなかったかと思います。

「特定のイベントが発生した場合に DMV から追加で情報を取得する」ということを実現したい場合、どのような方法があるか考えてみたところ、イベントによっては「WMI イベント警告」という機能を使えることを思い出したので、WMI イベント警告についてまとめてみたいと思います。

WMI イベント警告の前に WMI を把握する

WMI イベント警告は WMI (Windows Manaement Instrumentation) を SQL Server から使用することで、WMI のイベントが発生した際に、SQL Server で SQL Server エージェントジョブを実行することができる機能です。

これにより、SQL Server で特定のイベントが発生した場合にジョブを実行することで、イベント駆動で情報を取得することができるようになります。

WMI イベント警告については、WMI Provider for Server Events によって構成されており、この機能が使用されることで、SQL Server で発生したイベントを WMI イベントとして、SQL Server で活用することができるようになります。

WMI Provider for Server Events について では、次のように記載されています。

SQL Server でイベント通知を生成できるイベントはすべて、WMI で利用できるようになります。

WMI では多数のクラスを利用することができますが、WMI イベント警告については、すべての WMI のクラスが使用できるわけではなく、イベント通知を行うクラスのみが使用することがでます。

これについては、SQL Server Agent WMI Event alert でも記載されています。

WMI イベント警告を作成する際には、次の UI を使用することができます。

image

WMI 名前空間は、デフォルトでは「\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER」が設定されていますが、WMI を使用する際に活用する機会の多い名前空間である「root\cimv2」を指定することもできます。

ただし、名前空間は以下のすべてのクラスを使用することはできないようです。

SQL Server Agent WMI Event alert では、「Win32_LogicalDisk」を使用しようとして、Error 14511 が発生しています。

指定された @wmi_namespace で @wmi_query を実行できませんでした。クエリで選択されたイベント クラスがこの名前空間に存在すること、およびクエリの構文が正しいことを確認してください。

このエラーが発生している場合は、クエリに指定したクラスがイベントクラスではない可能性があります。

イベントクラスかどうかの取得方法ですが、次のようなコマンドが使用できるのではないでしょうか。

Get-WmiObject -Namespace "root\cimv2" -List | where Derivation -Contains "__Event"
Get-WmiObject -Namespace "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER" -List | where Derivation -Contains "__Event"

 

「\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER」の名前空間配下のクラスについては、ほとんどイベントクラスのようなので、問題はないかと思いますが、それ以外の名前空間を指定する場合には、イベントクラスかどうかは意識しておく必要があるのではないでしょうか。

 

WMI について確認する際のドキュメント

WMI イベント警告は、WMI のイベント通知を SQL Server でキャッチする機能となり、どのようなイベントの情報を取得するか / イベント内のどのプロパティを活用するかの情報が必要となります。

WMI イベント警告を使用するためには WMI / WQL についてのスキルが必要となります。

WMI にどのような名前空間 / クラスがあるかについては、PowerShell を使用することができます。

PowerShell を使用した WMI の操作については、第 7 章 – WMI の操作 で解説がされていますので、PowerShell から WMI (CIM) を操作する方法はこの情報が参考になるかと思います。

また、Windows には WBEMTEST というツールがあり、使用方法については WBEMTEST の概要 から確認することができます。

このツールを使用すると、指定した名前空間に含まれているクラスや、クラスで取得できるプロパティ等の情報が確認できますので、WMI のテストを行うのに活用すると便利です。

image

また、WMI については、WMI コントロールを使用した名前空間のセキュリティの設定 に記載されているように「WMI コントロール」の MMC を使用して情報を確認することもできます。

名前空間の確認や、名前空間に対してのアクセス許可については、MMC 経由で実施することができます。

image

最後に確認しておくドキュメントととしては、WMI と SQL です。

WMI イベント警告を使用する際に、情報の取得は WQL で記述をする必要があります。

WQL の構文については、このドキュメントツリーから確認できます。

WMI イベント警告を使用するための WMI についての基本的な情報についてはこれらのドキュメントから確認することができます。

WMI イベント警告を使用するための SQL Server エージェントの準備

WMI イベント警告では、特定のイベントが発生した場合に、SQL Server エージェントジョブを実行するという設定ができます。

image

SQL Server エージェントジョブでは、ジョブ ステップでのトークンの使用 に記載されているように、ジョブステップ内でトークンを使用することができ「WMI(プロパティ名)」というような形トークンを指定することで、WMI イベント警告が発生した際に、イベント内の特定の情報を取得することができます。

ただし、SQL Server エージェントの初期設定で、ジョブステップ内でトークンを使用しようとすると、ジョブステップの実行時に次のエラーが発生します。

ステップ 1 の実行を開始できません (理由: 変数 WMI(TextData) が見つかりません).  ステップは失敗しました。

英語だと Variable WMI(TextData) not found になります

SSMS で SQL Server エージェントのプロパティを開くと確認できるのですが、デフォルトの設定では「トークンの置き換え」が有効化されておらず、WMI イベント警告で発生したイベントのプロパティを取得することができません。

有効化については、SQL Server エージェントのプロパティ ([警告システム] ページ) でも記載されていますが、次の設定を有効にする必要があります。

image

この設定を有効にすることで、SQL Server エージェントジョブのステップ内で、WMI のプロパティの情報を取得することができます。

WMI イベント警告の設定

ここまでの内容で WMI イベント警告を使用するに際しての、WMI についての情報を確認しました。

それでは、「ブロッキングが発生した際に、ロックの詳細を取得する」という WMI イベント警告を作成してみたいと思います。

WMI イベント警告の作成については、次のドキュメントを確認してください。

Blocked Process Report イベント クラス を取得できるようにするため、事前に blocked process threshold サーバー構成オプション の設定は実施しています。

WMI イベント警告で使用可能なイベントの確認

WMI イベント警告は、クエリ (WQL) を記述して、取得するイベントを指定する必要があります。

今回は「\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER」の名前空間の「BLOCKED_PROCESS_REPORT」というイベントが発生した場合の設定を行っています。

image

クエリで使用するイベントですが、WMI の情報から確認することもできますが、「\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER」の名前空間で利用可能なイベントについては sys.event_notification_event_types (Transact-SQL) で確認することができます。

ブロッキングの情報が取得できそうなイベントとして、「BLOCKED_PROCESS_REPORT」の存在が確認できますね。

image

WMI イベント警告の作成

次のクエリを実行して、実際に WMI イベント警告を作成してみます。

EXEC msdb.dbo.sp_add_alert @name=N'Blocked Report', 
		@message_id=0, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
		@wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT'
GO

 

実行すると警告システムとして、指定した WMI イベント警告が作成されます。

image

WMI イベント警告ですが、次のようなクエリが実行され、メッセージの受信が行われているようです。

WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue), TIMEOUT 5000

上記のクエリでは、イベントを作成していますが、イベントを検知した場合に実行されるジョブは作成していないため、応答はブランクとなっています。

image

ジョブの作成

WMI イベント警告が作成出来たら、そのイベントが発生した場合に実行されるジョブを作成します。

今回、「ブロッキングが発生した場合に、ロックの詳細をテーブルに格納する」という動作を実現したいため、データを格納するために次のテーブルを作成しておきます。

CREATE TABLE [dbo].[LockCapture](
	[collect_date] [datetime] NOT NULL,
	[request_session_id] [int] NOT NULL,
	[status] [nvarchar](30) NOT NULL,
	[event_info] [nvarchar](max) NULL,
	[parameters] [smallint] NULL,
	[program_name] [nvarchar](128) NULL,
	[host_name] [nvarchar](128) NULL,
	[last_request_start_time] [datetime] NOT NULL,
	[last_request_end_time] [datetime] NULL,
	[resource_type] [nvarchar](60) NOT NULL,
	[resource_subtype] [nvarchar](60) NOT NULL,
	[resource_database_id] [int] NOT NULL,
	[resource_description] [nvarchar](256) NOT NULL,
	[resource_associated_entity_id] [bigint] NULL,
	[resource_lock_partition] [int] NULL,
	[request_mode] [nvarchar](60) NOT NULL,
	[request_type] [nvarchar](60) NOT NULL,
	[request_status] [nvarchar](60) NOT NULL,
	[request_reference_count] [smallint] NOT NULL,
	[request_lifetime] [int] NOT NULL,
	[request_exec_context_id] [int] NOT NULL,
	[request_request_id] [int] NOT NULL,
	[request_owner_type] [nvarchar](60) NOT NULL
) ON [PRIMARY] 
GO

次に、以下のようなクエリでジョブを作成します。(今回は TESTDB という DB に上記のテーブルを作成していることを想定してジョブを作成しています)

USE [msdb]
GO

DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Capture Lock Info', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'SET QUOTED_IDENTIFIER  ON
DECLARE @text xml= N''
$(ESCAPE_SQUOTE(WMI(TextData)))
''

SELECT
	GETDATE() AS collect_date,
	tl.request_session_id,
	es.status,
	ib.event_info,
	ib.parameters,
	es.program_name,
	es.host_name,
	es.last_request_start_time,
	es.last_request_end_time,
	tl.resource_type,
	tl.resource_subtype,
	tl.resource_database_id,
	tl.resource_description,
	tl.resource_associated_entity_id,
	tl.resource_lock_partition,
	tl.request_mode,
	tl.request_type,
	tl.request_status,
	tl.request_reference_count,
	tl.request_lifetime,
	tl.request_exec_context_id,
	tl.request_request_id,
	tl.request_owner_type
INTO #LockCapture
FROM 
	sys.dm_tran_locks  AS tl
	INNER JOIN sys.dm_exec_sessions AS es
		ON es.session_id = tl.request_session_id
	OUTER APPLY sys.dm_exec_input_buffer(request_session_id, NULL) AS ib
WHERE 
	request_session_id IN(
		@text.value(''(//blocking-process/process/@spid)[1]'', ''int''),
		@text.value(''(//blocked-process/process/@spid)[1]'', ''int'')
	)
	AND NOT (resource_type=''DATABASE'' AND request_mode = ''S'')

IF EXISTS(SELECT 1 FROM #LockCapture WHERE request_status = ''WAIT'')
BEGIN
	INSERT INTO LockCapture SELECT * FROM #LockCapture
END', 
		@database_name=N'TESTDB', 
		@flags=0

EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
GO

ジョブのステップの中では、「$(ESCAPE_SQUOTE(WMI(TextData)))」というトークンを使用しており、これにより、WMI イベント内のプロパティを取得することができます。

今回取得している TextData には、Blocked Process Report 相当の内容が出力されていますので、そこから Bloking / Blocked のセッション ID を取得して、該当のセッションのロック情報を取得して、テーブルに格納しているというものになります。

WMI イベント警告にジョブの設定

ジョブを作成したら、作成していた WMI イベント警告にジョブを設定します。

image

この設定を行うことで、WMI イベント警告に指定したイベントが発生するとジョブが実行されて、ロックの情報がテーブルに格納されます。

動作を確認

ここまでの設定を有効化しておくと、ブロッキングが発生したタイミングで、ロックの情報がテーブルに格納されるようになります。

イベント通知でブロッキングが通知されるタイミングと DMV で情報が取得されるタイミングが微妙にずれることがありますので、すべてのロックが取得できるわけではないのですが、情報が取得できると次のようなクエリで、ブロッキングが発生した際に、対象となったセッションで取得されていたロック競合の情報を確認することができます。

SELECT
	T1.*
FROM
	testdb.dbo.LockCapture  AS T1
	INNER JOIN(
		select * from testdb.dbo.LockCapture 
		where 
		request_status = 'WAIT'
	) AS T2
	ON T2.resource_description = T1.resource_description AND T2.collect_date = T1.collect_date
ORDER BY collect_date ASC

 

image

まとめ

WMI イベント警告を使用すると特定のイベントが発生した場合に、SQL Server エージェントジョブを実行させることができ、それにより、任意の処理を実行させることができます。

また、WMI イベント警告は、トークンの情報を取得することで、発生したイベント内のプロパティを取得することも可能ですので、イベント内の情報を活用することもできます。

WMI イベント警告による通知の取得は、タイミングによっては実際にイベントが発生したタイミングと通知があるタイミングが多少ずれることはありそうですが、イベント駆動で処理を行わせたい場合には活用できるのではないでしょうか。

Share

Written by Masayuki.Ozawa

7月 18th, 2021 at 5:08 pm

Posted in SQL Server

Tagged with

«
»

Leave a Reply