セージ の メモ書き

メモこそ命の恩人だ

SQL Server - ラッチ

docs.microsoft.com

ラッチ (Latch)

  • Latch:閂(かんぬき)
  • ページの整合性を保証するための機能。
  • ページアクセスの際にラッチが動作する。
  • SQL Server エンジン側が行う機能。ユーザー側で制御できない。

ラッチモード

記号 名称 内容
KP 保持ラッチ
(Keep)
参照中のページの破棄を防ぐ
最も軽量なラッチ
SH 共有ラッチ
(Share)
ページの情報を参照するために必要なラッチ
SH 同士なら競合しない(共有ロックと同じイメージ)
UP 更新ラッチ
(Update)
メタデータ関連のページを更新するためのラッチ
EX 排他ラッチ
(Exclusive)
ページに情報を書き込むために必要なラッチ
INSERT/UPDATE/DELETEで発生する
DT 破棄ラッチ
(Destroy Latch)
ページを削除するためのラッチ
  • 5種類のラッチモードがある。
  • SH・EX は、ユーザーのクエリによるページアクセスで発生する。
  • KP・UP・DT は、エンジン側が処理する。

ラッチ競合

ラッチ KP SH UP EX DT
KP ×
SH × ×
UP × × ×
EX × × × ×
DT × × × × ×
  • ラッチモードには互換性がある。
  • 互換性がない場合、ラッチ競合が発生する。
  • ラッチの処理が終了するまで、一方の処理が待機する。


確認用クエリ

sys.dm_os_wait_stats

sys.dm_os_wait_stats (Transact-SQL) - SQL Server | Microsoft Learn

列名 内容
wait_type 待機の種類
waiting_tasks_count 待機数
wait_time_ms 総待機時間 (ミリ秒)
signal_wait_time_ms 待機スレッドが実行を開始するまでの時間 (ミリ秒)
-- 待機数のあるラッチモードを表示
SELECT * 
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE '%LATCH%' 
AND waiting_tasks_count > 0;
-- 値のクリア
dbcc SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');


実験

排他ラッチの確認

以下の環境で、スクリプトを実行する。

-- ラッチ競合させるため、レコードサイズ小さめで登録
IF object_id(N'SampleTable1') is not null DROP TABLE SampleTable1;
CREATE TABLE SampleTable1 (Id INT PRIMARY KEY, Data1 BINARY(100)) ON SampleScheme1(Id);
GO

-- データを登録
DECLARE @Count INT = 1;
WHILE @Count <= 10000 BEGIN
    INSERT INTO SampleTable1 (Id, Data1) VALUES (@Count, 0x00);
    SET @Count += 1;
END
CHECKPOINT;
GO

-- 70レコード/データページとなることを確認
SELECT * FROM SampleTable1 CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%);


-- 値のクリア(確認前に以下を実行)
CHECKPOINT;
dbcc SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  • 排他ラッチを確認するため、異なるレコードに並列アクセスする。
    • 同じレコードにアクセスすると、ロックになる。
    • ラッチの影響を見るので、異なるレコードにアクセスする。

(Sample.ps1:PowerShell で実行する場合)

Workflow CallSqlcmd()
{
    foreach -parallel ($i in 1..50) {
        sqlcmd -U sa -P 1234 -S localhost\SAMPLE -d SampleDb -i C:\Sample.sql -v Param1=${i}
    }
}
(Measure-Command{ CallSqlcmd }).TotalMilliseconds

(Sample.sh:Bash で実行する場合)

#!/usr/bin/bash


#!/usr/bin/bash
for i in {1..1}; do
    winpty sqlcmd -U sa -P 1234 -S localhost\\SAMPLE -d SampleDb -i Sample.sql -v Param1=i &
done
wait

(Sample.sql

-- 同じページに並列アクセス => 排他ラッチの待機数が上昇する
DECLARE @Id INT = $(Param1);
-- 異なるページに並列アクセス  => 排他ラッチの待機数が上昇しない
--DECLARE @Id INT = $(Param1) * 70;

DECLARE @Count INT = 1;
WHILE @Count <= 1000 BEGIN
    UPDATE SampleTable1 SET Data1 = 0x00 WHERE Id = @Id;
    SET @Count += 1;
END

以下で待機数と待機時間の増加を確認。

SELECT * FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE '%LATCH%' AND waiting_tasks_count > 0;

以下、PAGELATCH_EX の待機情報。

並列数 試行回数 待機数 待機時間(ミリ秒)
10 1 2512 25
10 2 3477 39
10 3 3594 44
50 1 6109 57
50 2 7404 74
50 3 6965 72
  • PAGELATCH_EX の値が増加することを確認。
  • 50並列の場合、1スレッドあたりの待機時間について...
    • 平均67.6ミリ秒
    • 67.6÷50=1.35ミリ秒
    • なので、ほとんど待機していない。
  • PAGELATCH_EX 以外の値はほとんど増加しないことを確認。
  • 異なるページに並列アクセスした場合、排他ラッチが増加しないことを確認。



以上