並列処理の最大限度(maxdop)とは
SQL Server では、並列プランを実行する際に使用するプロセッサ数の上限値を決める設定があります。
[サーバーのプロパティ]の[詳細設定]の[並列処理の最大限度]の設定値が該当します。
(以下、max degree of parallelismの略でmaxdopと呼びます。尚、クエリヒントのmaxdopは今回は無関係です。）
規定値は0となっており、この場合は使用可能なすべてのプロセッサが使われます。（ただし最大64）
並列プランの場合、基本的には使用するプロセッサ数が多いほどCPU負荷は上昇し、代わりに実行速度が速くなる傾向があります。
そのため、maxdopの設定値はサーバー負荷と実行速度とのトレードオフとなっており、高い値を設定すれば良いというわけにはいかないところに意思決定の難しさがあります。
maxdopの設定値については、以下のガイドラインが存在します。
出典：max degree of parallelism サーバー構成オプションの構成
基本的にはこのガイドラインに従って設定することになるかと思いますが、ワークロードの性質によっては別の設定値のほうが全体のパフォーマンスを最適にできる可能性も考えられるかと思います。
そこで、maxdopの複数の設定値の候補から、ベストな値をみつける方法を考えてみました。
適切なmaxdopの選定方法
方法はいたってシンプルです。
「ガイドラインに従うと8だが、CPUリソースには余裕がある状況なので16を試してみたい」というシナリオを想定します。
設定値の変更によって、サーバー全体の実行速度にどういった変化があったかを比較できれば、納得感のある意思決定ができると考えました。
そこで、SQLServer:Batch Resp Statisticsというパフォーマンスオブジェクトを使用します。
※SQL Server2012以降でサポートされています。
この値は、パフォーマンスモニターを使って確認することができます。
パフォーマンスモニター起動後、緑色の+マークを押して、[SQLServer:Batch Resp Statistics]を選択し、[<すべてのインスタンス>]を選択した状態で[追加]を押して[OK]ボタンを押すと表示されるようになります。
ただし、折れ線グラフだとわかり辛いので、表示形式を切り替えて（↓の赤枠の箇所を何回か押す）レポート形式でみます。
今回はCPUリソースには余裕があるという前提で、クエリの実行時間(Elapsed Time）にだけ着目します。
出典
各カウンターの値の意味は分かりやすいです。「Batches >=000000ms & <000001ms」なら「応答時間が 0 ミリ秒以上 1 ミリ秒未満の SQL バッチ数」となります。
この分布が、maxdopの設定変更の前後でどう変わったかをみれば良さそうです。
ただし、この値は累積値のため、特定の期間の値を算出するためには二点間の差分をとる必要があります。
DMVでも同様の値がとれるため、値を保存しておくには以下のクエリを使用すると便利です。
select * from sys.dm_os_performance_counters
where object_name = 'SQLServer:Batch Resp Statistics'
あとは、maxdop=8と16とでそれぞれ実行速度の分布情報を取得し、「16のほうが実行速度の分布に改善がみられ、かつCPUリソースにも依然として余裕がある」という結果になれば、maxdopを16に変更しても良いかと思います。
また、実はほとんど変わらなかった、という結論になることもあるかもしれません。
いずれにせよ、この方法を使うことで、以前よりも納得感をもってmaxdopを設定できるのではと考えています。