SE の雑記

SQL Server v.Next のプレビューが開始された際に「Adaptive Query Processing」というものを目にしたのですが、「あれ、今どういうステータスなんだっけ」というのが気になったので少し調べてみました。

Adaptive Query Processing については、
What’s Adaptive Query Processing? (Dear SQL DBA Episode 21)
BRK3288: SQL Server v.Next with Support on Linux, Windows and Containers
(58:00 ぐらいから触れられています)
Adaptive Query Processing は、適応されたクエリの処理というものになり、

• パフォーマンスを向上させるためのクエリの処理の見積もり生成に対してのチャレンジ
• 繰り返し実行されるクエリで、許可されたワークスペースメモリが複製されることによる、過剰または小さな割当を避ける
• 結合を高速化するために、テーブルのデータの大小にあわせた結合方法に調整する

というようなものとなるようです。
現在、EAP から Preview の申し込みを受け付けられているようですね。
https://aka.ms/AdaptiveQPPreview
この適応クエリ処理ですが、Preview の申請をしなくても、SQL Server v.Next  であれば「Batch Mode Adaptive Memory Grant Feedback」という機能につていては試すことができるようになっています。
Introducing Batch Mode Adaptive Memory Grant Feedback
↑の記事は、去年の段階で公開されており見た記憶があるのですが、すっかりスルーしていました。。。。
安定のこちらのブログでも公開されていますね。
Columnstore Indexes ? part 93 (“Batch Mode Adaptive Memory Grant Feedback”)
試すことができる機能は、バッチモードにおける適応されたメモリ許可のフィードバックということで、クエリ実行時の「許可されたメモリ」(ワークスペースメモリー) のサイズを、クエリ実行に合わせて適応されるというものになるようです。
「行モード」の処理では、想定しているデータを取ることができなかったため、、現状は、列ストアインデックスを設定して、「バッチモード」で動作するクエリを使用する必要があるのかなと思います。
本機能は、v.Next の最新の互換性レベルである「140」にすることで動作します。
クエリを実行する際、「初回コンパイル時のパラメーター」を使用して、実行プランが生成され、その中には、クエリで使用するワークスペースのメモリの情報も含まれています。
以下のようなクエリの実行を例としてみます。

EXEC sp_executesql
N'SELECT TOP 100 L_PARTKEY FROM LINEITEM WHERE L_SHIPDATE BETWEEN @param1 AND @param2 GROUP BY L_PARTKEY ORDER BY MAX(L_ORDERKEY) DESC',
N'@param1 date, @param2 date',
@param1 = '1998-08-10',
@param2 = '1998-08-10'
GO
EXEC sp_executesql
N'SELECT TOP 100 L_PARTKEY FROM LINEITEM WHERE L_SHIPDATE BETWEEN @param1 AND @param2 GROUP BY L_PARTKEY ORDER BY MAX(L_ORDERKEY) DESC',
N'@param1 date, @param2 date',
@param1 = '1998-01-01',
@param2 = '2001-12-31'
GO

 
このときに使用される実行プランを確認してみます。

少量データの検索→大量データの検索 が実行されるケースとなり、実行プランについては、少量データの検索に合わせた形で生成が行われます。
許可されたメモリについては「70,744 KB」許可されていることが確認できます。

少量データの検索に対してはメモリを確保しすぎなため、警告が出ていますが、2 回目の大量データの検索に関しても、このメモリが使用されます。
それでは、大量データの検索→少量データの検索にするとどうなるでしょうか。

大量データを検索した場合「91,880 KB」のメモリが許可されたことが確認できます。

そのため、2 回目に実行している少量データに関してもこれだけのメモリが許可されることになり、このケースの場合あデータサイズに適していないメモリサイズが要求され、少量データの検索を複数同時に実行した場合などに、必要以上のメモリを確保するプランとなっているため、本来そこまでメモリを取得しなくてもよいものに対して、メモリ確保が行われ、メモリの要求待ちが発生する可能性が出てくることになります。
それでは、互換性レベルを「140」にして、「Batch Mode Adaptive Memory Grant Feedback」が使われるようにするとどうなるかを試してみたいと思います。
互換性レベル 130 の状態で、少量のデータ検索を連続して実行した場合は、以下のような実行プランで各回が実行されます。

2 回目以降の実行に関しても、初回コンパイル時の実行プランが使用されるため、すべての回で警告が出力されています。
 

初回のクエリ実行に関しては互換性レベル 130 の場合と同様です。


2 回目以降については、互換性レベル 130 の場合と違い、警告が出力されていません。

これが、「Batch Mode Adaptive Memory Grant Feedback」による、メモリ許可のフィードバックによるクエリの適応となるようです。
初回の実行では「70,744 KB のメモリ」が許可されましたが、これについては、確保しすぎではないかというような警告が発生しています。
これを学習し、2 回目の実行では、「5,280 KB」のメモリ許可というように調整がされています。


拡張イベントの「memory_grant_updated_by_feedback」から、メモリ許可が調整されたことが確認できます。


今回は確保しすぎでは挙動となっているため、減らしています。

今まではクエリのリコンパイルを明示的に実行 / プランガイドを使用してプランを補正 / メモリ許可のオプションを指定 というようなことをして、実行プランの補正をすることがあったかと思いますが、v.Next では、明示的な補正をしなくても調整をしてくれるという機能が含まれるようです。(補正される操作は特定のものになると思いますが。)
それでは、「少量データ→大量データの検索」を実行した場合の挙動を確認してみます。
このケースでは、少量データの検索で生成されたプランを大量データの検索でも利用して入りているため、メモリ上のワーク領域が不足し tempdb が使用されたケースとなります。


大量データの検索は 3 回連続で実行しているのですが、3 回目の実行では、警告が消えていることが確認できますね。

この情報は 1 ～ 4 回目までのメモリ許可の情報ですが、各回の実行で許可されているメモリのサイズが変わっていることが確認できますね。

• 1 回目の実行 : 少量データの検索であり、許可されたメモリサイズを少量データ向けに調整
• 2 回目の実行 : 少量データ向けのメモリサイズで実行したがメモリ不足が発生しているため、メモリサイズを調整
• 3 回目の実行 : 調整したメモリサイズでは、メモリ不足が発生しているため、さらにサイズを調整
• 4 回目の実行 : 調整したメモリサイズでメモリ不足が発生せずに警告が消えている

というような流れで調整されているのかと思います。
「memory_grant_updated_by_feedback」「spilling_Report_to_memory_grant_feedback」の 2 種類の拡張イベントを取得した結果が以下になります。

メモリが不足し、ディスクにあふれた場合 (tempdb が使用された場合) に、「spilling」が発生していると思いますが、先ほどの実行プランと同様に、2 回発生している (2 回目と3 回目の実行) に発生しており、メモリのサイズを増やすフィードバックが行われていることが確認できますね。
なかなか面白い機能ですね。