SE の雑記

SQL Server には、cost threshold for parallelism という、並列プランを使用するかの閾値のオプションがあります。

クエリのコスト プランが cost threshold for parallelism の現在の値より小さくても、並列プランが選択されることがあります。 並列プランまたは直列プランのどちらを使用するかが、最適化プロセスの初期段階で算出されたコストの推定値に基づいて決定された場合に、このようなことが起こります。 詳細については、「クエリ処理アーキテクチャ ガイド」をご覧ください。

と記載がある通り、この設定よりコストが小さくても並列プランが選択されることがあるようですが、並列プランを使用するかどうかを判断する要因の一つとなります。

本投稿では、並列のコストについてみていきたいと思います。

並列クエリについては 並列クエリ処理 が公式のドキュメントで情報がまとまっているものになるかと。

SQL Server のクエリ最適化

SQL Server では、クエリの最適化は次の 2 種類があります。

• TRIVIAL  (自明のプラン)
• FULL

TRIVIAL プラン最適化

クエリ実行時にクエリの最適化を行う処理は、リソースに負荷がかかる処理です。
そのため、「SELECT COUNT(*) FROM LINEITEM」というような、実行可能なプランが一つであると、SQL Server が判別できるようなクエリについては、クエリの最適化をスキップし、実行時のコストを抑える動作となっています。

これが TRIVIAL プラン最適化となります。
実行プランの「最適化レベル」が「TRIVIAL」となっている場合は、プラン最適化によるリソース消費を抑える形で実行プランが生成されクエリの実行が行われます。

FULL プラン最適化

SQL Server が TRIVIAL プランではないと判断した場合、FULL プラン最適化が行われます。
TRIVAL プランでない場合「最適化レベル」は「FULL」になっているかと思います。

FULL プラン最適化のフェーズに入った場合、クエリオプティマイザがコストを考慮しながら使用するプランの選定を行います。
FULL プラン最適化のフェーズに入った場合は、コストの考慮が行われ、並列プランを使用するかの判断を行うようになります。

FULL プラン最適化のフェーズ

FULL プラン最適化ですが、Exploration Stage と呼ばれ、TRIVIAL プランのプランが見つからなかった場合に、コストベースの最適化が行われます。

このステージは 3 種類のフェーズで構成されます

• Search 0 (TP) : トランザクション処理フェーズ
• Search 1 (QP) : クイックプランフェーズ
• Search 2 (FULL) : 完全最適化

Search 0 は一般的には 3 つ以上のテーブルが結合されている場合に使用されるようで、それ以下のテーブルの操作については Search 1 以降が実行されます。

Search 1 の最適化フェーズでもコストが高いと判断された場合は、Search 2 のフェーズの最適化が行われることになります。

Search 0 では、シリアルプランでのクエリ実行が検討され、Search 1 / 2 のフェーズに入った場合に、並列プランの利用が検討されることになります。

並列プランで実行されているクエリについては Search 1 / 2 の最適化フェーズが行われているということになります。

sys.dm_exec_query_optimizer_info

クエリオプティマイザーがどのような最適化を行ったかですが、sys.dm_exec_query_optimizer_info という DMV から確認をすることができるようになっています。

select * from sys.dm_exec_query_optimizer_info
where counter in('trivial plan', 'search 0', 'search 1', 'search 2')

この DMV を使用することで、クエリがどのような最適化フェーズで実行されているのかを確認することができます。

クエリ単位で最適化フェーズを確認

DMV で確認ができるのは、インスタンス全体でどのような最適化のフェーズが実行されたかの状況となり、実行したクエリがどの最適化フェーズが行ったかを確認するには、クエリの前後で sys.dm_exec_query_optimizer_info を挟んで実行して情報を取得することで確認を行うことができます。

この方法は自分以外が使用していない検証環境であれば、差分を求めることで情報を算出することができますが、複数のユーザーが使用している環境では、今回実行したクエリでどのような最適化が行われたのかを判断することが難しいです。

クエリ単位で最適化フェーズの情報を確認するためには、トレースフラグ 3604 / 8675 を使用します。

例としては次のようなクエリです。

以下のクエリは、OPTION に QUERYTRACEON でトレースフラグを指定していますが、「DBCC TRACEON(3604, 8675)」でも問題ありません。

SELECT TOP 10000 COUNT(*) FROM 
LINEITEM
INNER JOIN PART ON P_PARTKEY = L_PARTKEY
INNER JOIN SUPPLIER ON S_SUPPKEY = L_SUPPKEY
WHERE L_ORDERKEY >= 1000000
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8675)

QUERYTRACEON / DBCC TRACEON のどちらを使用する場合でも共通の注意点は「クエリのコンパイルを行わせること」です。

クエリのコンパイルが行われないと、上記のフラグを設定していても最適化は行われない (既に最適化が実施済み) ため、情報が出力されません。

トレースフラグで情報が出力されるのは「クエリのコンパイルが行われた場合」となりますので、情報が出力されない場合は、明示的なリコンパイルや DBCC FREEPROCCACHE 等でキャッシュをクリアして、クエリがコンパイルされるようにしてください。

クエリのコンパイルが行われる際に、トレースフラグが指定されていると次のような情報をメッセージとして取得できます。

この情報がクエリ最適化のフェーズと、各フェーズで算出されたコスト情報が出力されているものとなります。

今回、ポイントとなるのは、次の 3 行です。

end search(0),  cost: 4924.48 tasks: 485 time: 0 net: 0 total: 0 net: 0.011
end search(1),  cost: 4813.96 tasks: 1921 time: 0 net: 0 total: 0 net: 0.023
end search(1),  cost: 3103.13 tasks: 3580 time: 0 net: 0 total: 0 net: 0.036

このクエリでは、3 つのテーブルを JOIN しているため、Search 0 (TP) の最適化フェーズが実行されています。

Search 0 のコストでは高いと判断されたため、Search 1 (QP) の最適化フェーズが実行されていることが確認できます。

Search 1 については、2 行出力されているのが、今回のクエリのポイントでとなります。

Search 1 の最適化フェーズでは、クエリを並列実行するかの判断が行われます。

最初の Search 1 の最適化フェーズでは、コストが 4813.96 となっています。

このコストが cost threshold for parallelism を超えた場合、並列プランを採用する最適化フェーズが行われ、Search 1 を並列プランを使用したもので最適化が行われます。

• 最初の Search 1 : シリアルプランを使用する最適化
• 2 番目の Search 1 : 並列プランを使用する最適化

というようなイメージでよいかと。

cost threshold for parallelism による並列化の調整

ここまでの内容で、最適化フェーズごとのコストを取得することができました。

それでは、cost threshold for parallelism を使用して、並列プランの調整を行ってみたいと思います。

今回実行しているクエリの Search1 のシリアルプランの最適化のコストは「4813.96」でした。

それでは、次のようなクエリを実行してみます。

EXEC sp_configure 'cost threshold for parallelism', 4814
RECONFIGURE WITH OVERRIDE
GO

SELECT TOP 10000 COUNT(*) FROM 
LINEITEM
INNER JOIN PART ON P_PARTKEY = L_PARTKEY
INNER JOIN SUPPLIER ON S_SUPPKEY = L_SUPPKEY
WHERE L_ORDERKEY >= 1000000
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8675)

先ほどの実行結果では、Search 1 が 2 セット表示されていましたが、今回は 1 セットしか表示されていません。

今回のクエリでは、クエリを実行する前に cost threshold for parallelism を「4814」に変更しています。

Search 1 の実行時のコストは「4813.96」です。

並列クエリを実行するコストである 4814 を超えていないためクエリはシリアルプランとして実行されます。

実際の実行プランを確認しても、シリアルプランとして実行されていますね。

それでは、クエリを次のように変更してみます。

EXEC sp_configure 'cost threshold for parallelism', 4813
RECONFIGURE WITH OVERRIDE
GO

SELECT TOP 10000 COUNT(*) FROM 
LINEITEM
INNER JOIN PART ON P_PARTKEY = L_PARTKEY
INNER JOIN SUPPLIER ON S_SUPPKEY = L_SUPPKEY
WHERE L_ORDERKEY >= 1000000
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8675)

今回の実行では Search 1 が 2 セット表示されています。つまり並列プランを使用する最適化のフェーズが行われたということになります。

並列コストの閾値を「4813」に変更したことで、Search 1 のシリアルプランのコストである「4813.96」を「0.96」超えました。

これにより、Search 1 の並列クエリを使用する最適化フェーズの実行が行われます。

実際にの実行プランを確認すると、並列プランとして実行されていることが確認できますね。

cost threshold for parallelism の設定値によって、シリアルプラン / 並列プランのどちらが使用されるかについては、このような実行を行ってみるとイメージがつきやすいのではないでしょうか。

ちなみにcost threshold for parallelism の初期値は 5 だったはずですので、今回みたいに大きい値を設定しながら微妙なラインを狙っていくとかは普通やらないと思います。