連載
» 2016年10月12日 05時00分 UPDATE

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

本連載は、「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)。

photo 図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のトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。


Copyright© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。