追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その4
こんにちは、nagino です。
このテーマも、なんだかんだでその 4 になりました。
が、一番の王道で基礎である手法について言及するのを、すっかり失念していました。
というわけで、今回の手法は、インデックスの再構築・再編成です。
インデックスは木構造をとることでデータアクセスの高速性を得られますが、実体はディスクに書かれたページ単位のデータです。
データの更新や挿入が行われてもデータの論理的な並び順は保証されますが、物理的な並び順は徐々に断片化していきますし、ページ分割によってページあたりのデータ量が少なくなることでディスクアクセスやキャッシュ量が増えて、パフォーマンスが劣化します。
そのため、定期的にインデックスのメンテナンスを行うことで、パフォーマンスの向上が期待できます。
できれば日次で実行しておきたいところです。
インデックスの再編成は、リーフページの物理的な並び順を論理的な並び順と一致させます。
そのためインデックススキャンのパフォーマンスの向上が期待できます。
オンラインで実行でき、また負荷もそれほどではないため、毎晩実行してしまうのも一手です。
インデックスの再構築は、インデックスを作り直します。
こちらはインデックスを使用したシークなどのパフォーマンスも向上しますので効果は大きいですが、代わりに負荷も大きいです。
そのため、システムの負荷が少ない日曜の日中などに行うなど、実行タイミングを考慮する必要があります。
ただ、小規模システムであれば、極端な話ですが日次で実行してしまうのもありです。
一応詳細については以下にまとまっています。
http://msdn.microsoft.com/ja-jp/library/ms189858.aspx
上記では一定の断片化が発生するまでは再構築しなくて良いとありますが、よくある（そして無茶苦茶な）「とにかく最速に」という要求であれば、日次で再構築してしまったほうが良かったりします。
その分、インデックスのメンテナンス中のパフォーマンスや使用制限を検討しないといけなくなりますが、データベースはデータの倉庫ですから、整理整頓しておくことで処理の高速化を図るということですね。
追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その3補足
こんにちは、nagino です。
前回紹介したインデックス付きビューですが、Edition による差異が場合によってはクリティカルなため、補足します。
インデックス付きビューを作成した場合、Enterprise Edition と Developer Edition のオプティマイザでは、クエリでそのインデックス付きビューを指定していなくても使用するとコストが下がると判断した場合、インデックス付きビューが使用されます。
Standard Edition など他の Edition ではクエリでインデックス付きビューを明示的に指定し、NOEXPAND ヒントを使用している必要があります。（NOEXPAND ヒントは必須ではないかも・・・）
このため、開発環境は Developer Edition、本番環境は Standard Edition といった状況では、開発時は問題ないのに本番ではパフォーマンスが劣化するという状況に陥ります。
http://technet.microsoft.com/ja-jp/library/cc917715.aspx
http://msdn.microsoft.com/ja-jp/library/ms181151.aspx
Enterprise Edition はライセンス料は高いですが、インデックス付きビューのようなプログラムを改変できないアプリケーションに対してパフォーマンス向上の手段があるという点で非常に強力です。
追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その3
こんにちは、nagino です。
需要があるのかないのか分かりませんが、未整理に思いつくものから触れていく本シリーズ、3 回目です。
使える Edition に制限があったりするのですが、今日はインデックス付きビューについてです。
これは、作り方としてはビューにインデックスを付与するようなイメージなのですが、ビューとは名ばかりの実体のある表です。
例えば階層構造の部署に関して、関連する表を結合して利用することが多い場合、あらかじめ結合済みのインデックス付きビューを用意しておくと、検索時に結合処理が不要になるため高速化ができます。
ただし、実体が作られるために、更新時のパフォーマンス低下と、ディスク容量が必要になります。
使いどころはその特徴から分かるとおり、更新頻度が低いが結合して使用することが多いマスタや、更新頻度はほどほどであってもとにかく検索速度が求められる状況です。
ただし、特定クエリ毎に最適なインデックス付きビューを用意していくと、データがキャッシュに乗らなくなりますし、ディスク容量の増加によるディスク I/O の速度低下もおきますので、使いどころかどうかよく検討する必要があります。
追加コスト 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 負荷などを測定しながら、これらのパラメータによる影響・効果を測定してみてください。
ただし、これらはいずれも処理の分担の仕方の変更に過ぎませんので、環境によっては効果が無いことや逆効果となることが多々ありますので、その点も留意してください。
追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その1
こんにちは、nagino です。
効果や使用可能性などは考慮せず順不同で思いついた順に記述する本シリーズです。
状況次第では逆効果になることもありますので、私もご使用になる際は、ご自身の判断・責任・検証の下でお願いします。
さて、今回は tempDB について考えて見ます。
tempDB は、結構多くのケースで使用されています。
ソート、ハッシュ結合、トリガ、CTE、などなど・・・。
詳細は以下に列挙されています。
http://technet.microsoft.com/ja-jp/library/ms345368.aspx
このため、2 点ポイントがあることがわかります。
-1. サイズ
tempDB の OS から見た実体は、tempdb.mdf という単なるファイルです。
ですからファイルサイズが不足すると、自動拡張が行われます。
tempDB はハッシュ結合の中間テーブルなどに使われるため、非常に大きなサイズを必要とすることがありますので、自動拡張は実際起こりえます。
自動拡張の困る点は、ディスク I/O が大量に発生するため、ボトルネックになりやすいということです。
また、自動拡張を断続的に繰り返すと、tempdb.mdf というファイルが HDD 上でフラグメンテーションを起こすため、ディスク I/O のパフォーマンスが劣化するという問題もあります。
このため、あらかじめファイルサイズを大きくしておき、自動縮小を無効（これはデフォルトで無効）にしておくことで、自動拡張の時間を省くことができます。
どの程度のサイズが必要かは、事前に負荷テストなどを行った際に予測して設定されることをお勧めします。
なお、自動拡張自体は無効にしないことを強くお勧めします。
万が一容量不足になると、クエリが失敗します。
-2. 同時実行性
複数のクエリが並列で処理される場合、tempDB の空き待ちが発生するようです。
tempDB はファイル単位でロックがかかるようでして、そのためファイルグループ内に最大並列処理数（通常は CPU コア数）のファイルを作成することで、その待ちを無くすことができるようです。
そこまでシビアな環境は経験が無いのですが、以下に言及があります。
http://www.atmarkit.co.jp/fdb/rensai/drk07/drk07_1.html
# ちなみに、上記の @IT の連載は秀逸ですので、熟読をお勧めします。
# いきなりネタ晴らしですが、本シリーズも元ネタは上記連載が多いです。
なお、ドライブが多数ある場合は、上記のファイルを配置するドライブを分散させることで、ディスク I/O の負荷を分散させることが期待できます。