【SQL Server】ロックとラッチのトレース

SQL Serverの待機イベントの一つであるロックとラッチがどのような順番で獲得され、解放されるのかトレースしてみました。

拡張イベントの設定

トレースするにあたって拡張イベントを使います。
今回試すSQLはセッション63にて実行するので、フィルタしてあります。

CREATE EVENT SESSION [Lock_and_Latch_Mon] ON SERVER 
ADD EVENT sqlserver.latch_acquired(
    ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([class],(28)) AND [sqlserver].[session_id]=(63))),
ADD EVENT sqlserver.latch_released(
    ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([class],(28)) AND [sqlserver].[session_id]=(63))),
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(63))),
ADD EVENT sqlserver.lock_released(
    ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(63)))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL13.INS_NISHI2016\MSSQL\Log\Lock_and_Latch_Mon.xel')
WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

拡張イベントによるトレース

拡張イベントを作成できたら、セッションを開始します。
f:id:utiowa:20170219170926p:plain

セッション開始後、状況をリアルタイムで確認するために、「ライブデータの監視(L)」を選択します。
f:id:utiowa:20170219170939p:plain

対象のセッションで今回試したいクエリを実行してみます。
f:id:utiowa:20170219170951p:plain

先ほど「ライブデータの監視(L)」で開いたウィンドウに結果が表示されているかと思います。
f:id:utiowa:20170219171009p:plain

クエリ実行後は拡張イベントによる監視を停止します。
f:id:utiowa:20170219171000p:plain

トレース結果の確認

拡張イベントによるトレース結果から処理の流れを結果を見てみます。
①OBJECTに対するインテント共有ロック獲得
②ページ：75715に対する共有ページラッチ獲得
③ページ：5615に対する共有ページラッチ獲得
④ページ：75715に対する共有ページラッチ解放
⑤ページ：5009に対する共有ページラッチ獲得
⑥ページ：5615に対する共有ページラッチ解放
⑦ページ：5171に対する共有ページラッチ獲得
⑧ページ：5009に対する共有ページラッチ解放
⑨ページ：5171に対するインテント共有ロック獲得
⑩ページ：5171に対するインテント共有ロック解放
⑪ページ：5171に対する共有ページラッチ解放
⑫OBJECTに対するインテント共有ロック解放

実行プランは下記のようなシンプルなものです。
f:id:utiowa:20170219171139p:plain

今回のクエリはClustered Index Seekのみであり、このクラスターインデックスの奥行は4であるためページラッチの動きは以下のようなイメージになるかと思います。
f:id:utiowa:20170219171306p:plain
f:id:utiowa:20170219171311p:plain
f:id:utiowa:20170219171317p:plain
f:id:utiowa:20170219171324p:plain