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

こんにちは、nagino です。

効果や使用可能性などは考慮せず順不同で思いついた順に記述する本シリーズです。

状況次第では逆効果になることもありますので、私もご使用になる際は、ご自身の判断・責任・検証の下でお願いします。

さて、今回は tempDB について考えて見ます。

tempDB は、結構多くのケースで使用されています。

ソート、ハッシュ結合、トリガ、CTE、などなど・・・。

詳細は以下に列挙されています。

このため、2 点ポイントがあることがわかります。

-1. サイズ

tempDB の OS から見た実体は、tempdb.mdf という単なるファイルです。

ですからファイルサイズが不足すると、自動拡張が行われます。

tempDB はハッシュ結合の中間テーブルなどに使われるため、非常に大きなサイズを必要とすることがありますので、自動拡張は実際起こりえます。

自動拡張の困る点は、ディスク I/O が大量に発生するため、ボトルネックになりやすいということです。

また、自動拡張を断続的に繰り返すと、tempdb.mdf というファイルが HDD 上でフラグメンテーションを起こすため、ディスク I/O のパフォーマンスが劣化するという問題もあります。

このため、あらかじめファイルサイズを大きくしておき、自動縮小を無効（これはデフォルトで無効）にしておくことで、自動拡張の時間を省くことができます。

どの程度のサイズが必要かは、事前に負荷テストなどを行った際に予測して設定されることをお勧めします。

なお、自動拡張自体は無効にしないことを強くお勧めします。

万が一容量不足になると、クエリが失敗します。

-2. 同時実行性

複数のクエリが並列で処理される場合、tempDB の空き待ちが発生するようです。

tempDB はファイル単位でロックがかかるようでして、そのためファイルグループ内に最大並列処理数（通常は CPU コア数）のファイルを作成することで、その待ちを無くすことができるようです。

そこまでシビアな環境は経験が無いのですが、以下に言及があります。

# ちなみに、上記の @IT の連載は秀逸ですので、熟読をお勧めします。

# いきなりネタ晴らしですが、本シリーズも元ネタは上記連載が多いです。

なお、ドライブが多数ある場合は、上記のファイルを配置するドライブを分散させることで、ディスク I/O の負荷を分散させることが期待できます。

野良エンジニアの足跡