SQL Server のクエリストアには「サイズベースクリーンアップ」という機能があります。
これについては、クエリ ストアを使用する際のベスト プラクティス に次のように記載が行われています。
重要
[最大サイズ (MB)] の制限は、厳密には適用されません。 ストレージ サイズは、クエリ ストアでディスクにデータが書き込まれる場合にのみ確認されます。 この間隔は、 [データのフラッシュ間隔 (分)] オプションによって設定されます。 クエリ ストアでストレージ サイズの確認の合間に最大サイズの制限を超えた場合は、読み取り専用モードに移行します。 [サイズ ベースのクリーン アップモード] が有効になっている場合は、最大サイズの制限を適用するクリーンアップ メカニズムもトリガーされます。
クエリストアの設定には最大サイズを指定する必要があり、この最大サイズに達した場合に、サイズベースのクリーンアップを行うことができます。(デフォルトはサイズベースクリーンアップが有効)
この動作について調べる必要があったので、確認した内容をまとめておきたいと思います。
サイズベースのクリーンアップの制限は厳密に適用されるわけではない
冒頭に記載したドキュメント内の注記にありますが「サイズベースのクリーンアップの制限は厳密に適用されるものではない」という動作になります。
指定したサイズになると自動的にクエリのクリーンアップを行い、常に READ_WRITE (情報の取得が行われる状態) に保つというものではありません。
「データのフラッシュ間隔 (分)」のタイミングで、「最大サイズを超えているため、READ_ONLY に設定するかの判断」「最大サイズを固定る場合、クリーンアップメカニズムのトリガー」が行われます。
そのため、最大サイズを設定していも、次のような状態になることがあります。
- 一時的に最大サイズを超える
- READ_ONLY の状態になる
動作の確認
それでは実際に動作を確認してみたいと思います。
今回は次の設定となっています。
- クエリストアの取り込みモード : すべて
- 最大サイズ : 300 MB
- データのフラッシュ間隔 : 5 分
この設定となっている状態で、PowerShell から次のスクリプトを実行して、アドホックなクエリを大量に実行させます。
1..5 | % {
Start-job -ScriptBlock {
1.. 20000 | % {
Add-Type -AssemblyName System.Web
$con = new-Object System.Data.SqlClient.SqlConnection("Server=localhost;Integrated Security=SSPI;database=TESTDB")
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandText = ("SELECT '{0}' AS c, * FROM T1" -f [System.Web.Security.Membership]::GeneratePassword(100, 0))
[void]$cmd.ExecuteNonQuery()
$con.Close()
}
}
}
While((get-job -State Running) -ne $null){
Write-Host ("{0} : {1}/{2} Waiting...." -f (Get-Date), (Get-Job -State Running).Count, (Get-Job).Count)
Start-Sleep -Seconds 10
}
Get-Job | Remove-Job
状態の取得には次のクエリを実行します。
SELECT actual_state_desc, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options SELECT OBJECT_NAME(object_id) AS object_name, row_count, reserved_page_count * 8 / 1024 AS reserved_page_MB, used_page_count * 8 / 1024 AS used_page_MB, SUM(reserved_page_count * 8 / 1024) OVER() AS total_reserved_MB FROM sys.dm_db_partition_stats WHERE OBJECT_NAME(object_id) LIKE 'plan[_]%' AND OBJECT_SCHEMA_NAME(object_id) = 'sys' AND index_id = 1 AND row_count > 0 ORDER BY object_id ASC
このクエリは、クエリストアの設定からサイズ音使用状況を取得、クエリストアを構成するシステムテーブルの使用状況を確認するためのクエリとなります。
スクリプトを実行して、クエリストアの使用状況をウォッチしていると、次のように、最大サイズを超えるタイミングがあることが確認できます。
今回取得した情報であれば、最大サイズは 300MBですが、使用状況については 333MB になっていることが確認できます。
最大サイズを超えていますが、「READ_WRITE」の状態を維持していることが確認できます。
「READ_ONLY」になるのはデータのフラッシュ間隔を基準にしたタイミングとなりますので、フラッシュ間隔に達するまでは READ_ONLY にはなりません。
フラッシュ間隔になると、「READ_ONLY」になり、サイズベースのクリーンアップの動作が開始されます。
クエリストア関連のバックグラウンドダスクについてはコマンドが「QUERY_STORE」で始まっている傾向があり、次のようなクエリで実行中の要求があるかを取得することができます。
SELECT * FROM sys.dm_exec_requests WHERE command LIKE 'QUERY STORE%'
サイズベースのクリーンアップについてはコマンドが「QUERY STORE SIZE BASED CLEANUP」として実行が行われているバックグラウンドタスクとなり、この要求が動いている場合には、バックグラウンドでサイズベースのクリーンアップが実行されている状態となります。
バックグラウンドのクリーンアップについて拡張イベントも提供されており、「query_store_size_retention_query_deleted」のようなイベントを取得することでクリーンアップの状況を確認することができます。
クリーンアップが進み、現在のサイズが最大サイズを下回ると、「RED_ONLY」から「READ_WRITE」に切り替わり、情報の取得が再開されます。
クエリ ID 単位で削除が実行されるようで、最大サイズを超えた分が少量であれば、クリーンアップの時間はそれほどかかりません。
しかし、最大サイズを大きく超えている場合 (フラッシュ間隔が来る前にクエリストアの情報登録が継続している場合) は、クリーンアップは、シングルスレッドで動作しているようで、クリーンアップが完了するのはもかなり時間がかかるようです。
今回使用したようなクエリを活用すると、クエリストアの動作の調査がいろいろとできそうですね。