SQL Server 2019 CTP 3.1 のデータベースエンジンの新機能として「OPTIMIZE_FOR_SEQUENTIAL_KEY」というインデックスのオプションが追加されました。
このオプションは、インデックスへの挿入を行う際に発生する Last page insert によるページラッチの競合を改善する効果のあるオプションとなっています。
詳細については、CREATE INDEX のヘルプの Sequential Keys に記載されています。
この動作を検証するために次のような PowerShell のスクリプトで試してみました。
(Ctrl+C で停止することで結果が取得できます)
$Mode = "OFF" $sql = @" select * from sys.dm_os_wait_stats where wait_type IN('PAGELATCH_EX', 'WRITELOG') "@ $initialize_sql = (@" USE IndexTest; DROP TABLE IF EXISTS T1; CREATE TABLE T1 ( C1 int IDENTITY, C2 datetime2, CONSTRAINT PK_T1_C1 PRIMARY KEY CLUSTERED (C1) WITH(OPTIMIZE_FOR_SEQUENTIAL_KEY = {0}) ); CREATE NONCLUSTERED INDEX NICX_T1 ON T1 (C2) WITH(OPTIMIZE_FOR_SEQUENTIAL_KEY = {0}) CHECKPOINT; "@ -f $Mode) $ErrorActionPreference = "Stop" $con = New-Object System.Data.SqlClient.SqlConnection $con.ConnectionString = "Data Source=localhost;Database=IndexTest;Integrated Security=SSPI" $con.Open() $cmd = $con.CreateCommand() $cmd.CommandText = $initialize_sql [void]$cmd.ExecuteNonQuery() $cmd.CommandText = $sql $da = New-Object System.Data.SqlClient.SqlDataAdapter $dt = New-Object System.Data.DataTable $da.SelectCommand = $cmd Write-Host ("{0} : start (Mode : {1})." -f (Get-Date), $Mode) [void]$da.Fill($dt) $result = @() foreach($row in $dt.Rows){ $result += [PSCustomObject]@{ wait_type = $row.wait_type waiting_tasks_count = $row.waiting_tasks_count wait_time_ms = $row.wait_time_ms } } $dt.Clear() try{ while($true){ Start-Sleep -Seconds 1 } }catch{ }finally{ Write-Host ("{0} : stop (Mode : {1}).`n" -f (Get-Date), $Mode) [void]$da.Fill($dt) $row1 = $dt.Rows | ? wait_type -eq "WRITELOG" $row2 = $result | ? wait_type -eq "WRITELOG" Write-Host ("{0} Wait Count : {1:#0.0}, Wait Time (ms) {2:#0.0}" -f ` $row1.wait_type, ($row1.waiting_tasks_count - $row2.waiting_tasks_count), ($row1.wait_time_ms - $row2.wait_time_ms) ) $row1 = $dt.Rows | ? wait_type -eq "PAGELATCH_EX" $row2 = $result | ? wait_type -eq "PAGELATCH_EX" Write-Host ("{0} Wait Count : {1:#0.0}, Wait Time (ms) {2:#0.0}" -f ` $row1.wait_type, ($row1.waiting_tasks_count - $row2.waiting_tasks_count), ($row1.wait_time_ms - $row2.wait_time_ms) ) } $con.Close() $con.Dispose()
最初は、OFF の状態 (OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) の状態で大量の INSERT を実行して動作を確認してみます。
上述のスクリプトを実行している状態で、RML Utility の OStress を使用して負荷を発生させてみます。
Set-Location "C:\Program Files\Microsoft Corporation\RMLUtils" .\ostress.exe -Slocalhost -E -d"IndexTest" -Q"SET NOCOUNT ON;INSERT INTO T1 (C2)VALUES (GETDATE())" -n100 -r1000
初期状態ではこのような結果となりました。
ON にした状態では次のような結果となりました。
大量の INSERT を実行しているため、WRITELOG の待ち事象については、大差はありませんが、PAGELATCH_EX については、ON にした場合の方が待ち事象による待ち時間が減少 (9619ms → 4482ms) しています。
細かな実装の情報がないので詳細な動作が確認できていないのですが、シーケンシャルに連続した値が挿入されるような場合 (IDENTITY や日付のような連続した値がインデックスに挿入される) は、今回の追加されたオプションを追加することで、待ち事象が減少しスループットが向上することが数値として取得できているのは確認できたのではないでしょうか。