以前、次の投稿で並列クエリになるようにする調整方法を投稿しました。
最近、これらの情報を確認する機会がありましたので、改めて情報を整理しておきたいと思います。
Contents
並列クエリとは？
SQL Server の並列クエリについては クエリ処理アーキテクチャ ガイド の 並列クエリ処理 に記載されています。この情報の他には次の情報も参考となります。
- 論理操作および物理操作のプラン
- 表示演算子のリファレンス
- The Parallelism Operator (aka Exchange)
SQL Server のいくつかの操作では複数のワーカースレッドを使用してクエリ内の処理を実行することができます。
並列処理として実行された処理については、次の画像のように実行プラン内のアイコンが並列処理のマークがついたものとなります。
並列処理として実行された処理は、次の画像のように一つの処理が複数のスレッドで実行されます。
これにより CPU の使用時間は増加しますが、複数のスレッドによりデータの処理が行われるため処理時間の短縮につながる可能性があります。
高速なストレージを使用していても、1 スレッドで処理可能なスループットは上限がありますので、単一のクエリでストレージ性能を最大限活用するためには並列クエリによる複数スレッドの処理が必要となるケースがあります。
SQL Server が並列クエリを使用するかどうかについては、実行プランのコストをベースに制御が行われており、サーバー構成: 並列処理のコストしきい値 の設定 (cost threshold for parallelism) の設定を使用して並列プランとして実行するかどうかが決定されます。
cost threshold for parallelism はデフォルトでは 5 に設定されており、一般的には変更することはないため、初期設定のコストで SQL Server のオプティマイザに任せて並列クエリの使用可否が判断されるケースが大半ではないでしょうか。
手動で並列クエリになるように調整する方法
基本的には SQL Server のオプティマイザに任せて並列クエリの利用有無が変わってきます。
低コストなクエリでは並列クエリにならない可能性が高いのですが、ワークロードによっては低コストのクエリでも並列クエリ化をしたいケースがあります。
前述の「cost threshold for parallelism」の設定を小さくすることで、低コストなクエリでも並列クエリとして実行できるケースがありますが、この設定はインスタンス全体に影響を与えるものとなり影響範囲が大きくなるため、クエリ単位で制御をしたいことがあります。
そのようなケースでは次のような方法を使用することで、クエリ単位で並列クエリになるような調整ができる可能性があります。
- 「DBCC SETCPUWEIGHT」を使用することで、意図的に CPU コストを増加させる
- OPTION 句 で「QUERYTRACEON 8649」を使用する
- OPTION 句 で「USE HINT(‘ENABLE_PARALLEL_PLAN_PREFERENCE’)」を使用する
これらの方法を使用することで、クエリ単位で並列クエリとして実行される可能性が出てきます。
以下のクエリを例として、各方法を使用してみます。
DBCC FREEPROCCACHE GO SELECT COUNT(*) FROM LINEITEM WHERE L_ORDERKEY BETWEEN 10 AND 1000 GROUP BY L_COMMENT GO
このクエリはコストが低いクエリとなるため、通常の実行方法では各処理がシングルスレッドで実行されます。
このクエリを各方法で並列クエリ化を行ってみたいと思います。
DBCC SETCPUWEIGHT
DBCC FREEPROCCACHE GO DBCC SETCPUWEIGHT(1000) GO SELECT COUNT(*) FROM LINEITEM WHERE L_ORDERKEY BETWEEN 10 AND 1000 GROUP BY L_COMMENT GO
QUERYTRACEON 8649
DBCC FREEPROCCACHE GO SELECT COUNT(*) FROM LINEITEM WHERE L_ORDERKEY BETWEEN 10 AND 1000 GROUP BY L_COMMENT OPTION(QUERYTRACEON 8694) GO
ENABLE_PARALLEL_PLAN_PREFERENCE
DBCC FREEPROCCACHE
GO
SELECT COUNT(*) FROM LINEITEM WHERE L_ORDERKEY BETWEEN 10 AND 1000
GROUP BY L_COMMENT
OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
GO
これらの方法を使用することで、並列クエリのコストのしきい値を超えないクエリについても、以下のように並列クエリ化することができる可能性があります。
手動で並列化をする場合の注意点
アンドキュメントな手法ということを意識する
手動で並列化をする場合の注意でですが上述の 3 種類のいずれの方法も「アンドキュメント (Undocumented) 」な方法となり、Microsoft からサポートを受けることはできない方法となるということです。
「ENABLE_PARALLEL_PLAN_PREFERENCE 」については、ヒント句として使用していますが、クエリ ヒント (Transact-SQL) でも公開はされておらず、sys.dm_exec_valid_use_hints (Transact-SQL) でも公開されていないヒントとなります。
そのため、これらの方法は正規の並列クエリを実行するための方法ではないため、使用することで何らかの問題が発生した場合、サポートに連絡をしても、手動で並列化を行う方法を継続して使用することはできず「使用することをやめる」という対応しかできない可能性が高いです。
また、正規のコストで並列化を実施しているわけではありませんので、並列化を行っても次の画像のように各スレッドで均等に処理が行われず、処理のオーバーヘッドのみが増加する可能性があることも意識しておく必要があります。
必要となる権限
「DBCC SETCPUWEIGHT」「QUERYTRACEON 8649」(DBCC TRAEON) については、DBCC コマンドの実行が必要となるため、「sysadmin」の権限が必要となります。
「USE HINT(‘ENABLE_PARALLEL_PLAN_PREFERENCE’)」については、ヒント句として実行されるため、data_reader のようなデータ参照ができる少ない権限のユーザーでも使用することができます。
高い権限を持たせずに、並列クエリ化の実行を検討したいのであれば、「USE HINT(‘ENABLE_PARALLEL_PLAN_PREFERENCE’)」が候補として挙がるのではないでしょうか。