SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2019 CTP 3.1 で追加された OPTIMIZE_FOR_SEQUENTIAL_KEY の効果を確認してみる

without comments

SQL Server 2019 CTP 3.1 のデータベースエンジンの新機能として「OPTIMIZE_FOR_SEQUENTIAL_KEY」というインデックスのオプションが追加されました。

image
このオプションは、インデックスへの挿入を行う際に発生する 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

 
初期状態ではこのような結果となりました。
image
ON にした状態では次のような結果となりました。
image
大量の INSERT を実行しているため、WRITELOG の待ち事象については、大差はありませんが、PAGELATCH_EX については、ON にした場合の方が待ち事象による待ち時間が減少 (9619ms → 4482ms) しています。
細かな実装の情報がないので詳細な動作が確認できていないのですが、シーケンシャルに連続した値が挿入されるような場合 (IDENTITY や日付のような連続した値がインデックスに挿入される) は、今回の追加されたオプションを追加することで、待ち事象が減少しスループットが向上することが数値として取得できているのは確認できたのではないでしょうか。

Written by Masayuki.Ozawa

7月 1st, 2019 at 12:15 am

Posted in SQL Server

Tagged with ,

Leave a Reply