TEMPDBを配置したディスク領域が枯渇した（Read Committed Snapshot分離レベル編）（ファイル管理トラブル）：SQL Serverトラブルシューティング（22）

本連載は、「Microsoft SQL Server」で発生するトラブルを「どんな方法で」「どのように」解決していくか、正しい対処のためのノウハウを紹介します。今回は「Read Committed Snapshot分離レベルを採用したときのTEMPDB関連トラブル対処方法」を解説します。

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

連載バックナンバー

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

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

トラブル 16（カテゴリー：ファイル管理）：TEMPDBを配置したディスク領域の容量が枯渇した（Read Committed Snapshot分離レベル編）

目次

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

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

トラブルの実例：デッドロックが多発するシステムにおいて、ロック競合を起こす頻度を下げるために「Read Committed Snapshot分離レベル（*1）」を採用した（図16-1）。

図16-1　データベースのプロパティにある「スナップショット分離レベル」の設定画面。Read Committed Snapshot分離レベルは、「スナップショット分離を許可」「Is Read Committed Snapshot On」の双方を「True」にする。規定値はFalseである

　そのシステムでしばらく運用していたところ、TEMPDBを配置したディスク領域で「空き領域閾（しきい）値超過」の警告が発生。やがてディスクの空き領域がゼロとなり、いくつかの処理でディスク拡張に関わるエラーが発生した。

*1：Read Committed Snapshot分離レベルでは、更新ロックが掛かっているレコードに対する読み取りはロック待ちにならない。インスタンスは、更新ロックをかけているトランザクションが開始する「前のレコード」を、後続する読み取りのクエリに見せる仕組みで実現する

目次に戻る

トラブルの原因を探る

　このトラブルは、Read Committed Snapshot分離レベルを採用したことに由来し、バージョンストア（更新前レコード）が長時間TEMPDBに保存されていることが原因です。

　バージョンストアは参照される必要がなくなれば自動的に解放され、TEMPDBの領域を確保し続けることはありません。今回のケースでは、ロック競合を回避するためにRead Committed Snapshot分離レベルを採用しましたが、それでもロック競合が多発している場合はアプリケーションの実装に問題があると想定されます。アプリケーションがロックを持ち続ける理由には以下の要因があります。

• 短時間で動作終了するはずのクエリが長時間動いている
• トランザクションが開始してから終了するまでにユーザーの入力待ちがある
• トランザクションが分割できるにもかかわらず、1つのトランザクションとして実行されている

　このような課題を抱えているアプリケーションでは、Read Committed Snapshot分離レベルを採用したとしてもトランザクションが終了されません。結果としてバージョンストアがTEMPDB内に蓄積され続け、容量を消費してしまいます（図16-2）。

図16-2　「パフォーマンスモニター」で、バージョンストアの利用度が上がっていく様子を確認。このグラフでは、バージョンストアが蓄積されるにつれてTEMPDBの空き容量が減少し、最終的に枯渇してしまっている

目次に戻る

解決方法

　本トラブルの根本解決策は、「バージョンストアを蓄積させないために、トランザクションを短時間で終了させる」ことです。

　トランザクションは短いに越したことはありません。例えば、「ページ1」から「ページ3」まで入力させて完了となるWebアンケートの入力ページがあるとします。このときユーザーは、「ページ2」で放置してしまう可能性があります。トランザクションがオープンのままだと、ここに「ロック待ち」の要因が発生します。トランザクションは可能な限り分割することで、不要なロックを保持しない運用につながります。

　Read Committed Snapshot分離レベルは、短期的にはロック待ちの軽減につながります。しかし、状況によっては解決しないロック待ちもあります。例えば「更新ロックと更新ロックの競合」の場合はRead Committed Snapshot分離レベルでは回避できないので注意しましょう。また、Read Committed Snapshot分離レベルと、SQL Serverの既定動作である「Read Committed分離レベル」では、異なる読み取り一貫性が実装されているため、業務ロジック上で問題が発生しないかの検討が必要です。

「Read Committed分離レベル」と「Read Committed Snapshot分離レベル」の違い

　SQL Serverは、既定動作では「Read Committed分離レベル」で動作します。設定の変更によって、「Read Committed Snapshot分離レベル」を選択できます。どちらも「Read Committed」であることは変わりませんが、それぞれ何が違うのかを以下の図で示します（図16-3）。

図16-3　「Read Committed分離レベル」と「Read Committed Snapshot分離レベル」における、読み取り一貫性の違い。Read Committed分離レベルは「現在実行中のトランザクションが終了した時点」での読み取り一貫性を、Read Committed Snapshot分離レベルでは「現在実行中のトランザクションが開始する前」の読み取り一貫性を保証している

　Read Committed分離レベルとRead Committed Snapshot分離レベルは、後続のトランザクションが実行されるタイミングによって読み出すレコードが異なります。分離レベルを変更すると、アプリケーションが読み出すレコードが異なる可能性があります。「業務ロジック上での問題」とは、それが異なっても、業務上問題がないかを確認する作業のことです。

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

1. パフォーマンスカウンターからバージョンストアの肥大化を確認する
2. 長いトランザクションがバージョンストアを確保したままになっていないか確認する
3. アプリケーションの変更をし、トランザクションを短時間で終了するようにする

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

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

筆者紹介

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

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

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

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