TEMPDBを配置したディスク領域が枯渇した（ソートの繰り返し編）（ファイル管理トラブル）：SQL Serverトラブルシューティング（23）

連載バックナンバー

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で発生するトラブルについて、「なぜ起こったか」の理由とともに具体的な対処方法を紹介していきます。

トラブル 17（カテゴリー：ファイル管理）：TEMPDBを配置したディスク領域の容量が枯渇した（ソートの繰り返し編）

　「Windows Server 2012 R2」上に「SQL Server 2016 RTM」をインストールした環境を想定して解説します。

トラブルの原因を探る

　アプリケーションはSQL文を用いてデータ操作を行います。SQL Serverの内部では、SQL文は実行プランと呼ばれる内部動作手順に変換されます。その際には、実行プランで内部的にソート操作が行われることがあります。

　このトラブルは、オンラインアプリケーションがソートを何度も繰り返したことによって、一時作業領域であるTEMPDBが拡張し続けてしまったことが原因です。再起動すればTEMPDBは初期化されるので、しばらくは発生しなくなります。しかし何の対処もしなければ、ディスク容量の限界まで拡張した時点で再発します。再起動すると直ったかのように見えてしまうこと、「限界まで拡張された時点」が定期的ではないこと。ここが落とし穴になります。

解決方法

　本トラブルには、2つの解決策があります。

　1つは「ソートを使わない」ように処理を変更することです。SQL文は実行プランに変換されますが、ヒント句やクエリガイドを利用することで、内部的にソートをさせないよう工夫することです。

　もう1つの方法は、繁忙期のラッシュに耐えられるようにTEMPDBを設計／構成することです。TEMPDBを構成する上で決めるべき内容は以下の通りです。

• データファイルを配置する領域
• データファイルサイズ
• データファイルの数

　なお、TEMPDBに関わらず、データベースのファイルを配置するディスクは、必ずRAID（Redundant Arrays of Inexpensive Disks）構成で冗長性を担保する構成にしましょう。また、TEMPDBは複数の処理で同時に使われるので、なるべくI/Oの高速なディスクを使うことをお勧めします。

　では、どのくらいのデータファイルサイズが必要なのか。机上計算できる計算式はないのですが、例えば、今回のトラブルの原因であるTEMPDBの拡張に対応するために、「アプリケーションが稼働するピークを乗り越えられるだけのTEMPDBのサイズを最初から確保しておく」が指針になります。

　また、これからシステムを構築するならば、本番稼働前に「TEMPDBのサイズを決めるためのピーク性能をテスト」しておくこともお勧めします。テストでは、まずTEMPDBの自動拡張かつ拡張サイズを小さく設定してテストケースを実行し、自動拡張でどこまで大きくなるのかを見極めます。設定する値は、ピークテストで把握できたサイズのプラスα分を確保するようにします。

　一方、実稼働後にこのトラブル現象が発生した場合は、現象発生時のTEMPDBに別ドライブのデータファイルを追加し、不足を補いながら総サイズを確認します。データファイルの追加、分割をした場合は状況を見てオフライン時間帯にデータファイルの再配置を行い、現行環境の中で最適な形を構成します。

　データファイルサイズを定めたら、データファイルを分割する検討を行います。TEMPDBのファイル数は、「CPUコア数」または「8」のいずれか小さい方に設定するのがよいとされています。TEMPDBを利用すると、データファイル内に領域を確保します。領域管理を行う箇所への同時アクセスが限界以上に行われると、そこがボトルネックとなり、処理効率に影響を及ぼします。この管理領域を増やす／コアごと分散する手段が、データファイルの分割です。

　ちなみに、最新バージョンの「SQL Server 2016」では、TEMPDBの分割をインストール時に設定する項目が追加されました（図17-1）。

図17-1　SQL Server 2016では、インストール時にTEMPDBの設定を行う画面が追加された（それ以前のバージョンでも、インストール後に個別設定できる）