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

本連載は、「Microsoft SQL Server」で発生するトラブルを「どんな方法で」「どのように」解決していくか、正しい対処のためのノウハウを紹介します。今回は「TEMPDBを配置したディスク領域不足のエラーが発生した場合の対処方法（ソートの繰り返し編）」を解説します。

[内ヶ島暢之，ユニアデックス株式会社]

連載バックナンバー

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

→この他のファイル管理に関連するトラブルの実例を見る

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

目次

• トラブルの実例
• トラブルの原因
• 解決方法

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

トラブルの実例：繁忙期のオンライン業務中にTEMPDBを配置したディスク領域の空き容量不足エラーが発生し、処理が止まってしまった。再起動すると回復するのだが、再び同じエラーが発生する。根本原因を解決できない。

目次に戻る

トラブルの原因を探る

　アプリケーションは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の設定を行う画面が追加された（それ以前のバージョンでも、インストール後に個別設定できる）

「TEMPDBを配置したディスク領域にエラーが発生した（ソートの繰り返し編）」の場合の解決手順

1. 暫定的に別ドライブへデータファイルを追加する
2. 処理のピークを乗り越えられるサイズを見極める
3. データファイルの分割を行う
4. データファイルの再配置を行う

→この他のトラブルの実例を見る

本トラブルシューティングの対応バージョン：SQL Server 全バージョン

筆者紹介

内ヶ島 暢之（うちがしま のぶゆき）

ユニアデックス株式会社所属。Microsoft MVP Data Platform（2011〜 ）。OracleやSQL Serverなど商用データベースの重大障害や大型案件の設計構築、プリセールス、社内外の教育、新技術評価を行っていた。2016年4月よりIoTビジネス開発の担当となり、新しい仕事に奮闘中。ストレッチをして柔らかい身体を手に入れるのが当面の目標。

椎名 武史（しいな たけし）

ユニアデックス株式会社所属。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。