背景

SQL Serverに関するロックについて、以下の記事で説明しました。
SQL Serverのロックについて出来る限り分かりやすく解説
この記事の後半で、リアルタイムでブロッキングが発生している場合に情報を取得する方法についても説明しました。
ただ、この方法だと過去に発生したブロッキングについての情報は確認することができません。

本記事では、ブロッキングが発生した場合に後追いできるような仕組みの作り方について説明します。

※SQL Server2012以降が対象です。

手順

1.サーバーの設定を変更する

show advanced options サーバー構成オプションを1に設定（有効化）します。次に、blocked process threshold サーバー構成オプションに、任意の値を設定します。
値の単位は「秒」なので、↓の例だと、5秒以上ブロッキングされたクエリについてレポートするように設定します。

exec sp_configure 'show advanced options', 1
reconfigure

--5秒以上ブロッキングされたらレポートするように設定
exec sp_configure 'blocked process threshold (s)', 5
reconfigure

このクエリはどのDBで実行しても問題ありません。
オンライン操作ですので、プロダクション環境でダウンタイム無しで実行できます。
実行も、経験上は瞬時に完了します。

↑のようなメッセージが表示されればOKです。
一応、設定が反映されたことを以下のクエリで確認します。

select * from sys.configurations order by name

[value_in_use]カラムの値が、設定した秒数（今回でいうと5）になっていればOKです。

2.拡張イベントを設定する

SSMSで[管理]-[拡張イベント]-[セッション]を右クリックし、[新規セッション]をクリックします。

[全般]でセッション名を入力します。任意の名前でOKです。

[イベント]ページに移動し、[blocked_process_report]を選択します。
テキストで[blocked]と入力すると候補が絞り込まれるので見つけやすいです。
選択後、[>]マークを押してイベントを取得対象に入れます。
最後に[構成]を押します。

ブロッキングイベントが発生した際に、追加で取得したい情報を選択します。
以下の項目にチェックをつけておけば十分かと思います。

cliend_app_name
client_hostname
database_id
database_name
nt_username
query_hash
query_plan_hash
session_id
session_nt_username
sql_text
task_time
username

[データストレージ]ページに移動し、ターゲットの型で[event_file]を選択します。これは、イベント発生時に情報をどこに保存するか指定するオプションです。

保存するファイル名も任意でOKです。デフォルトでは、SQL Serverのエラーログが格納されるディレクトリに保存されるはずですが、フルパスで指定することも可能です。
ファイルの最大サイズ、ファイルの最大数を指定することで、この画像だと1GBx5で最大5GBの容量を使う設定にしています。
[ファイルのロールオーバーを有効にする]にチェックをつけることで、この5GBの範囲内で古いデータは削除され、常に最新のデータが5GBの容量の範囲内で保存され続けることになります。

[詳細設定]ページで、[複数のイベントの損失]にチェックをいれます。これにより、全てのイベントがキャッチされるわけではなくなりますが、代わりに情報取得時の負荷を減らすことができます。

最後に[OK]を押して設定完了です。

[セッション]を展開すると、作成した拡張イベントが表示されるかと思いますので、右クリックして[セッションの開始]を押すと、実際にブロッキング情報を収集し始めます。

3. ブロッキングが発生したら情報を確認してみる。

例えば、以下のようなクエリを2つのクエリウインドウで実行することでブロッキングを発生させます。

begin tran
	update Member set Sei = 'Test' where MemberID = 18629764

10秒程度実行し、停止します。

そのあと、稼働中の拡張イベントを展開し、[package0.event_file]をダブルクリックします。

先ほど発生させたブロッキングをイベントとしてキャッチできています。
[blocked_process]フィールドの値（赤枠）をダブルクリックすると、詳細が確認できます。

このXMLは、上部が「blocked-process」すなわち、ブロックされた側（被害者）の情報で、下部が「blocking-process」すなわち、ブロックした側（加害者）の情報を示しています。

それぞれ、クエリテキストも確認することができます。
今回は同じクエリでブロッキングを発生させたので、同じテキストが表示されていることが確認できます。

他にも、競合していたリソースが何なのか、そのリソースにどの種類のロックをかけようとしていたか等もこのXML上で確認することができます。

まとめ

拡張イベントを使ってブロッキングの発生を自動保存する仕組みづくりについて説明しました。
ファイルサイズに上限を持たせる＋ロールオーバーさせることで、ディスク容量の消費を一定に保ちつつ、ブロッキング情報を取得し続けることができます。
問題発生時の後追いに便利な仕組みです。

追記：SSMSが使えない環境下でクエリベースで拡張イベントを使用する場合

設定されている拡張イベントの確認

select a.*, (case when b.name is null then 'stop' else 'running' end) as status
from sys.server_event_sessions a
left join sys.dm_xe_sessions b on a.name = b.name
order by status

拡張イベントの設定

max_file_size:1ファイルあたりの最大ファイルサイズ(MB)
max_rollover_files:ファイルのロールオーバー数
例：ADD TARGET package0.event_file(SET filename=N'test',max_rollover_files=(8))

CREATE EVENT SESSION [blocked_process_report] ON SERVER 
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'blocked_process_report.xel',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

拡張イベントの有効化と無効化

ALTER EVENT SESSION [blocked_process_report] ON SERVER STATE=START
ALTER EVENT SESSION [blocked_process_report] ON SERVER STATE=STOP

取得したイベントの確認

select top 100
  object_name,
  cast(event_data AS xml) as event_data,
  dateadd(hour, +9, timestamp_utc) as timestamp
from
  sys.fn_xe_file_target_read_file('blocked_process_report*.xel', NULL, NULL, NULL)
order by
	timestamp_utc