投稿日：2023/10/05

はじめに

皆さん、初めまして。
システムサポートの中川と申します。
今回はSQL Serverの機能の1つである拡張イベントの設定方法と検証で得られた結果を紹介します。

拡張イベントって何？

拡張イベントはSQL Serverの動作に関する情報を収集する機能であり、パフォーマンス問題やDBの動作の監視が行えます。
拡張イベントが導入される前は、SQL トレース (SQL Trace)とSQL Server プロファイラーが使用されていましたが、現在は非推奨となっています。

なお、拡張イベントの作成や開始、実行はSQLベースだけでなく、SQL Server Management Studio (SSMS) でも行えますので、SQLに慣れていないユーザーでも簡単に作成することも可能です。

拡張イベントを作成しよう

今回は以下の環境で拡張イベントの検証を行いました。

Windows Server 2019
SQL Server 2019
SSMS 18系

拡張イベントで収集した情報はxelファイルとして保存することができます。
今回はファイルの保存先として　C:\ext_event　というフォルダを作成します。

SSMSを開き、[管理] > [拡張イベント] > [セッション]を選択し、新規セッションウィザードを押下します。
以下、GUIでの設定手順を記載しますが、最後にSQLも記載します。

新規セッションウィザード画面が出力されたら、次に進みましょう。

作成する拡張イベントの名前を指定します。
今回はブロッキング情報を取得するイベントを作成していきますので、block_eventとします。

収集する情報についての選択を行います。
多くのユーザーが使用すると想定される情報についてはテンプレートとして用意されているようです。
内容としてはSQL Profile相当のテンプレートや、クエリ、システム監視、ブロッキング情報のものが用意されています。

今回は敢えてテンプレートを使用せずに進めたいと思います。

次に収集するイベントをカスタマイズしていきます。
イベント ライブラリの検索窓に block~と入力すると、LIKE検索が行われますのでご自身で収集したいイベントを探してみてください。
 
グローバルフィールドのキャプチャは、特定のイベントが発生した際に収集する追加情報を指定する機能です。
例えばデータベースID、ユーザー名、クライアントアプケーション名などをキャプチャすることができます。
今回は、クエリハッシュ、SQLテキスト、ユーザー名を指定します。

セッションイベントのフィルターでは、イベントをキャプチャする際の絞り込み条件を加えることができます。
今回は特に指定せずに進めます。

冒頭にも記載しましたが拡張イベントでは、収集した情報をxelファイルとして保存することができます。
そのファイルの格納先を指定します。

作成する拡張イベントのサマリーが表示されるので、問題がなければ次に進みましょう。

ちなみに上記の手順で行った設定をSQLにしたものが↓になります。

CREATE EVENT SESSION [block_event] ON SERVER 
ADD EVENT sqlserver.blocked_process_report(
ACTION(sqlserver.query_hash,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'C:\ext_event\block_event.xel')
WITH (STARTUP_STATE=OFF)
GO

USE MASTER
GO
ALTER EVENT SESSION [block_event] ON SERVER 
STATE = start; 
GO

USE AdventureWorks2017
GO
-- Person.Personにロックが掛かる
BEGIN TRANSACTION
UPDATE Person.Person 
SET LastName = LastName;

USE AdventureWorks2017
GO
-- Person.Personにロックが掛かってるので待機する
SELECT TOP (1000) [LastName]
,[FirstName]
,[Title]
FROM Person.Person
WHERE FirstName = 'David'

USE MASTER
GO
ALTER EVENT SESSION [block_event] ON SERVER 
STATE = stop; 
GO