SQL Server 2019 以降のクエリストアでは、カスタムキャプチャの設定を行うことができるようになりました。これにより、クエリのキャプチャモードを「ALL」「AUTO」以外に「CUSTOM」で設定することができるようになります。
「CUSTOM」を使用した場合、キャプチャされるクエリの条件を従来より柔軟に設定することができます。
「CUSTOM」を設定した場合の設定観点について少しふれておきたいと思います。
クエリストアの活用については次のドキュメントが参考になります。
設定の考慮点
カスタムキャプチャの設定が必要となるシーン
カスタムキャプチャの設定ですが、私が携わる中で設定が必要となると思えたシーンは次の 2 つになります。
- 「ALL」では情報が取得されすぎ、「AUTO」では精度が低いため「ALL」と「AUTO」の中間の設定を行いたい
- アドホッククエリが多いため、「AUTO」の取得精度でもクエリストアの最大サイズに達してしまう
このような場合に、カスタムキャプチャの設定が必要になると感じました。
「AUTO」の場合の設定
「AUTO」にしている場合、複数回実行しているクエリの情報が取得されるようになりますが、どのような条件で取得が行われるかについては、クエリストアの設定 に記載されています。
- EXECUTION_COUNT = 30 実行 = 実行数
- TOTAL_COMPILE_CPU_TIME_MS = 1 秒 = ミリ秒単位のコンパイル時間
- TOTAL_EXECUTION_CPU_TIME_MS = 100 ms = ミリ秒単位の実行 CPU 時間
カスタムキャプチャの設定を行う場合は、この設定を起点にしてします。
上記のケースであれば、次のように設定を行います。
- 「ALL」では情報が取得されすぎ、「AUTO」では精度が低いため「ALL」と「AUTO」の中間の設定を行いたい
- 「AUTO」の取得精度より低い値を設定し、今までは取得対象となっていなかったクエリを取得対象とする
- アドホッククエリが多いため、「AUTO」の取得精度でもクエリストアの最大サイズに達してしまう
- 「AUTO」の取得精度より高い値を設定し、今までは取得対象となっていたクエリを取得対象外とする
基本的に「大量のアドホッククエリが実行されている環境」でなければ、「AUTO」にし、「クエリストアの最大サイズ」を調整することでサイズベースのクリーンアップの範囲内に収まり、クエリストアを「READ_ONLY」にすることなく、クエリの情報を取得することができます。
しかし、「大量のアドホッククエリが実行されている環境」では、「AUTO」にしていても、ある程度の時間のクエリ情報を取得するとクエリストアが「READ_ONLY」となり、ある程度の時間範囲のクエリ情報を取得するとサイズの上限に達してしまい、クエリストアが読み取り専用となってしまうことがあります。
最大サイズをある程度増やすことで読み取り専用となるのを回避することができるのであれば、それで問題はないのですが、最大サイズを調整してもいつまでも読み取り専用となってしまう場合には、取得対象となるクエリの調整が必要となることがあります。
最大サイズを調整しても、想定した日数分の情報が取得できずに読み取り専用となってしまう場合には、カスタムキャプチャの設定を検討してみては良いのではないでしょうか。