SQL Server でイベント発生時の情報を取得する方法としては、拡張イベント (xEvent) があります。
拡張イベントを使用することで、SQL Server で発生する様々なイベントを取得することができます。
拡張イベントは情報を取得する際に強力な機能ですが、「イベントが発生した際に任意の DMV から情報を取得する」ということができなかったかと思います。
「特定のイベントが発生した場合に DMV から追加で情報を取得する」ということを実現したい場合、どのような方法があるか考えてみたところ、イベントによっては「WMI イベント警告」という機能を使えることを思い出したので、WMI イベント警告についてまとめてみたいと思います。
Contents
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 を使用することができます。
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 のテストを行うのに活用すると便利です。
また、WMI については、WMI コントロールを使用した名前空間のセキュリティの設定 に記載されているように「WMI コントロール」の MMC を使用して情報を確認することもできます。
名前空間の確認や、名前空間に対してのアクセス許可については、MMC 経由で実施することができます。
最後に確認しておくドキュメントととしては、WMI と SQL です。
WMI イベント警告を使用する際に、情報の取得は WQL で記述をする必要があります。
WQL の構文については、このドキュメントツリーから確認できます。
WMI イベント警告を使用するための WMI についての基本的な情報についてはこれらのドキュメントから確認することができます。
WMI イベント警告を使用するための SQL Server エージェントの準備
WMI イベント警告では、特定のイベントが発生した場合に、SQL Server エージェントジョブを実行するという設定ができます。
SQL Server エージェントジョブでは、ジョブ ステップでのトークンの使用 に記載されているように、ジョブステップ内でトークンを使用することができ「WMI(プロパティ名)」というような形トークンを指定することで、WMI イベント警告が発生した際に、イベント内の特定の情報を取得することができます。
ただし、SQL Server エージェントの初期設定で、ジョブステップ内でトークンを使用しようとすると、ジョブステップの実行時に次のエラーが発生します。
ステップ 1 の実行を開始できません (理由: 変数 WMI(TextData) が見つかりません). ステップは失敗しました。
英語だと Variable WMI(TextData) not found になります
SSMS で SQL Server エージェントのプロパティを開くと確認できるのですが、デフォルトの設定では「トークンの置き換え」が有効化されておらず、WMI イベント警告で発生したイベントのプロパティを取得することができません。
有効化については、SQL Server エージェントのプロパティ ([警告システム] ページ) でも記載されていますが、次の設定を有効にする必要があります。
この設定を有効にすることで、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」というイベントが発生した場合の設定を行っています。
クエリで使用するイベントですが、WMI の情報から確認することもできますが、「\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER」の名前空間で利用可能なイベントについては sys.event_notification_event_types (Transact-SQL) で確認することができます。
ブロッキングの情報が取得できそうなイベントとして、「BLOCKED_PROCESS_REPORT」の存在が確認できますね。
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 イベント警告が作成されます。
WMI イベント警告ですが、次のようなクエリが実行され、メッセージの受信が行われているようです。
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue), TIMEOUT 5000
上記のクエリでは、イベントを作成していますが、イベントを検知した場合に実行されるジョブは作成していないため、応答はブランクとなっています。
ジョブの作成
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 イベント警告にジョブを設定します。
この設定を行うことで、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
まとめ
WMI イベント警告を使用すると特定のイベントが発生した場合に、SQL Server エージェントジョブを実行させることができ、それにより、任意の処理を実行させることができます。
また、WMI イベント警告は、トークンの情報を取得することで、発生したイベント内のプロパティを取得することも可能ですので、イベント内の情報を活用することもできます。
WMI イベント警告による通知の取得は、タイミングによっては実際にイベントが発生したタイミングと通知があるタイミングが多少ずれることはありそうですが、イベント駆動で処理を行わせたい場合には活用できるのではないでしょうか。