追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その2

こんにちは、nagino です。

今日は MDOP についてです。

一般な Microsoft な方々の間では MDOP というと Microsoft Desktop Optimization Pack のことを指しますが、SQL Server な方々の間では Max Degree Of Parallelism のことを指します。

もちろんここでも Max Degree Of Parallelism のことです。

これは閾値を設定し、それを超えた場合には複数の CPU コアによる分散クエリを実行するという設定です。

SQL Server は、多数の CPU コアがある環境でも実行計画のコストが MDOP より小さい場合は 1 つのコアのみを使用します。

これは、分散クエリでは各コアの処理結果が出揃うのを待ち、その後にその結果をマージするというオーバーヘッドがかかってしまうため、短時間のクエリでは分散クエリにしないほうがシステム全体として効率が良いから、という考えに基づいています。

それで、SQL Server 2005 / 2008 では、確かインストール直後ではこれが 5 に設定されています。

ですので通常は分散クエリとなることは少ないのですが、レポートや集計などで処理の重いクエリ等を実行する環境では、分散クエリとなっている可能性があります。

処理時間のかかるクエリを分散して処理すれば、そのクエリ単体の処理時間は短くなることが期待できますが、その分同時実行できるクエリの数は減ります。

また、コア別に CPU キャッシュがあるため、キャッシュのヒットミスや、他のコアのキャッシュに更新済みのデータがある場合は一度メモリに書き込んだ後にキャッシュの取得が必要であるなど、色々とオーバーヘッドがかかります。

特に SQL Server は NUMA に対応していますので、NUMA 環境下では特にコア別のキャッシュの扱いに注意する必要があります。

通常 OLTP 系の場合は一切分散クエリとしないよう 0 に設定するとシステム全体のパフォーマンスが向上することが期待できます。

一方使用者数が極端に少ない OLAP 系の場合は、適切な値に設定することで個々のクエリの処理時間を最適化することが期待できます。

OLTP 系でも夜間バッチなどがある場合は、ジョブなどでバッチ実行中だけ分散処理するように設定するという方法も検討する価値があります。

このように、同時実行要求数、平均処理時間、等々を検討のうえで設定を変えるだけでパフォーマンスが改善することがあります。

ちなみに、NUMA も関係するのですが、SQL Server では I/O 処理と計算処理で別々に Affinity Mask を設定できますので、CPU コアのキャッシュを生かすために I/O 処理をするコアを固定するという手法もあります。

コア別の CPU 負荷などを測定しながら、これらのパラメータによる影響・効果を測定してみてください。

ただし、これらはいずれも処理の分担の仕方の変更に過ぎませんので、環境によっては効果が無いことや逆効果となることが多々ありますので、その点も留意してください。