tempdb に使用するディスク領域の計画
このトピックでは、tempdb に必要なディスク領域の適切な量を決定するためのガイドラインについて説明します。さらに、このトピックでは実稼働環境でパフォーマンスを最適にするために tempdb を構成する方法の推奨事項と、tempdb 領域の使用状況を監視する方法について説明します。
tempdb の使用方法
tempdb システム データベースは、SQL Server のインスタンスに接続しているすべてのユーザーが使用できるグローバル リソースです。tempdb データベースはユーザー オブジェクト、内部オブジェクト、およびバージョン ストアの格納に使用されます。
ユーザー オブジェクト
ユーザー オブジェクトは、ユーザーによって明示的に作成されます。これらのオブジェクトは、ユーザー セッションのスコープ内、またはオブジェクトが作成されるルーチンのスコープ内に入れることができます。ルーチンは、ストアド プロシージャ、トリガー、またはユーザー定義関数です。ユーザー オブジェクトは、次のいずれかです。
ユーザー定義テーブルとインデックス
システム テーブルとインデックス
グローバル一時テーブルとインデックス
ローカル一時テーブルとインデックス
テーブル変数
テーブル値関数で返されるテーブル
内部オブジェクト
内部オブジェクトは、SQL Server ステートメントを処理するために必要に応じて SQL Server データベース エンジンにより作成されます。内部オブジェクトは、ステートメントのスコープ内で作成および削除されます。内部オブジェクトは、次のいずれかです。
カーソルまたはスプール操作用の作業テーブルと、一時的なラージ オブジェクト (LOB) 格納領域
ハッシュ結合操作またはハッシュ集計操作用の作業ファイル
インデックスの作成または再構築などの操作 (SORT_IN_TEMPDB を指定した場合) や、GROUP BY、ORDER BY、または UNION クエリにおける並べ替えの中間結果
各内部オブジェクトでは、少なくとも 9 つのページが使用されます (1 つの IAM ページと 8 ページ分のエクステント)。ページとエクステントの詳細については、「ページとエクステントについて」を参照してください。
バージョン ストア
バージョン ストアは、行のバージョン管理を使用する機能のサポートに必要なデータ行を保持するデータ ページのコレクションです。共通バージョン ストアとオンライン インデックス構築用のバージョン ストアの 2 つのバージョン ストアがあります。バージョン ストアには、次の内容が保持されます。
行のバージョン管理を伴う READ COMMITTED 分離レベルまたはスナップショット分離レベルを使用しているデータベースで、データ変更トランザクションによって生成される行バージョン
オンライン インデックス操作、複数のアクティブな結果セット (MARS)、および AFTER トリガーなどの機能に対してデータ変更トランザクションによって生成される行バージョン
次の表に、tempdb にユーザー オブジェクト、内部オブジェクト、または行バージョンを作成する SQL Server の機能を示します。可能な場合は、ディスク領域使用量の推定方法を説明します。
機能 |
tempdb の用途 |
追加情報 |
|---|---|---|
トリガーが有効な場合の一括読み込み操作 |
トリガーが有効になっていると一括インポートの最適化を使用できます。SQL Server は、トランザクションを更新または削除するトリガーに対して、行バージョン管理を使用します。削除または更新された各行のコピーが、バージョン ストアに追加されます。この表の「トリガー」を参照してください。 |
|
共通テーブル式のクエリ |
共通テーブル式は、単一の SELECT、INSERT、UPDATE、DELETE、CREATE VIEW の各ステートメントの実行スコープ内で定義される一時結果セットと考えることができます。 共通テーブル式のクエリのクエリ プランがスプール操作を使用してクエリの中間結果を保存する場合、この操作をサポートするために、データベース エンジンによって tempdb に作業テーブルが作成されます。 |
|
カーソル |
キーセット ドリブン カーソルと静的カーソルは、tempdb に作成された作業テーブルを使用します。キーセット ドリブン カーソルは、この作業テーブルを使用して、カーソル内の行を識別するキーのセットを格納します。静的カーソルは、作業テーブルを使用して、そのカーソルの結果セット全体を格納します。 カーソルに使用されるディスク領域は、選択したクエリ プランによって異なることがあります。クエリ プランが以前のバージョンの SQL Server と同じ場合、使用されるディスク領域はほぼ同じになります。 |
|
データベース メール |
この表の「Service Broker」を参照してください。 |
|
DBCC CHECKDB |
DBCC CHECKDB は、tempdb 作業テーブルを使用して、中間結果の保持と並べ替え操作を行います。 操作を行うために必要な tempdb ディスク領域を判断するには、DBCC CHECKDB WITH ESTIMATEONLY を実行します。 |
|
イベント通知 |
この表の「Service Broker」を参照してください。 |
|
インデックス |
インデックスを作成または再構築し (オフラインまたはオンライン)、SORT_IN_TEMPDB オプションを ON に設定する場合は、インデックスの構築に使用する中間の並べ替え結果の格納場所としてデータベース エンジンが tempdb を使用するように指定できます。SORT_IN_TEMPDB が指定されていて、並べ替えが必要な場合、tempdb には最も大きいインデックスを保持するのに十分なディスク領域に加えて、index create memory オプションの値に等しいディスク領域が必要です。詳細については、「インデックスのディスク領域の例」を参照してください。 テーブルとインデックスをパーティション分割できます。パーティション インデックスでは、SORT_IN_TEMPDB インデックス オプションが指定されていて、ベース テーブルにインデックスが固定されている場合、最も大きいパーティションでの中間の並べ替え実行結果を保持するのに十分な領域が tempdb に必要です。インデックスが固定されていない場合、すべてのパーティションの中間の並べ替え実行結果を保持するのに十分な領域が tempdb に必要です。詳細については、「パーティション インデックスの専用ガイドライン」を参照してください。 オンライン インデックス操作では、行のバージョン管理を使用して、他のトランザクションによる変更がインデックス操作に影響を与えないようにしています。行のバージョン管理により、読み取った行の共有ロックを要求する必要がなくなります。オンライン インデックス操作と同時にユーザーの更新操作と削除操作を実行するには、tempdb にバージョン レコード用の領域が必要になります。オンライン インデックス操作で SORT_IN_TEMPDB が使用され、並べ替えが必要な場合、tempdb には前述の並べ替え中間結果に使用する追加のディスク領域も必要です。クラスター化インデックスを作成、削除、または再構築するオンライン インデックス操作では、一時マッピング インデックスを構築および保持するために追加のディスク領域も必要になります。CREATE STATISTICS 操作と UPDATE STATISTICS 操作では、統計情報を構築する際、行のサンプルを並べ替える目的で tempdb が使用されます。詳細については、「インデックス DDL 操作に必要なディスク領域」を参照してください。 |
|
ラージ オブジェクト (LOB) データ型の変数とパラメーター |
ラージ オブジェクト データ型は、varchar(max)、nvarchar(max)、varbinary(max)text、ntext、image、および xml です。これらの型のサイズは最大 2 GB で、ストアド プロシージャ、ユーザー定義関数、バッチ、またはクエリで変数やパラメーターとして使用できます。LOB データ型として定義されたパラメーターと変数は、値が小さい場合はメイン メモリをストレージとして使用します。ただし、大きい値は tempdb に格納されます。LOB 変数およびパラメーターは、tempdb に格納されている場合は内部オブジェクトとして扱われます。sys.dm_db_session_space_usage 動的管理ビューに対してクエリを実行すると、特定のセッションの内部オブジェクトに割り当てられたページを報告できます。 SUBSTRING または REPLICATE などの一部の組み込み文字列関数で LOB 値を操作するときは、tempdb 内に中間一時ストレージが必要になります。同様に、行バージョンベースのトランザクション分離レベルをデータベースで有効にし、ラージ オブジェクトを変更すると、変更された LOB のフラグメントが、tempdb のバージョン ストアにコピーされます。 |
|
複数のアクティブな結果セット (MARS) |
1 つの接続で複数のアクティブな結果セットが発生することがあります。これを通常 MARS と呼びます。アクティブな結果セットが存在するときに、MARS セッションでデータ変更ステートメント (INSERT、UPDATE、DELETE など) が実行された場合、その変更ステートメントの影響を受けた行は tempdb 内のバージョン ストアに格納されます。この表の「行のバージョン管理」を参照してください。 |
|
クエリ通知 |
この表の「Service Broker」を参照してください。 |
|
クエリ |
SELECT、INSERT、UPDATE、および DELETE の各ステートメントが含まれるクエリは、内部オブジェクトを使用して、ハッシュ結合、ハッシュ集計、または並べ替えを行うための中間結果を格納できます。 クエリの実行プランがキャッシュされている場合、プランに必要な作業テーブルがキャッシュされます。作業テーブルがキャッシュされると、テーブルは切り捨てられ、再使用するために 9 つのページがキャッシュに残ります。これにより、クエリを次回実行する際のパフォーマンスが向上します。システムのメモリが少ない場合、データベース エンジンは実行プランを削除して、関連する作業テーブルを削除できます。 |
|
行のバージョン管理 |
行のバージョン管理は、次の機能をサポートするために使用される一般的なフレームワークです。
アクティブなトランザクションで行バージョンにアクセスする必要がある限り、その行バージョンは tempdb バージョン ストアに保持されます。現在のバージョン ストアの内容は、sys.dm_tran_version_store に返されます。バージョン ストア ページはグローバル リソースなので、ファイル レベルで追跡されます。sys.dm_db_file_space_usage で version_store_reserved_page_count 列を使用して、バージョン ストアの現在のサイズを表示できます。バージョン ストア クリーンアップでは、特定バージョンへのアクセスを必要とする、実行時間が最も長いトランザクションを考慮する必要があります。バージョン ストア クリーンアップに関連する実行時間が最も長いトランザクションを調べるには、sys.dm_tran_active_snapshot_database_transactions の elapsed_time_seconds 列を確認します。Transaction オブジェクトのカウンター Free Space in Tempdb (KB) および Version Store Size (KB) は、tempdb 内の行バージョン ストアのサイズと増加率の監視に使用できます。詳細については、「SQL Server: Transactions オブジェクト」を参照してください。 行のバージョン管理を行うために必要な tempdb の領域の量を推定するには、まずアクティブなトランザクションがすべての変更をバージョン ストアに保持することを考慮する必要があります。これは、後で開始するスナップショット トランザクションが古いバージョンにアクセス可能であることを意味します。さらに、アクティブなスナップショット トランザクションがある場合、スナップショットが開始したときにアクティブになるトランザクションにより生成されたすべてのバージョン ストア データも保持する必要があります。 次に基本的な式を示します。 [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction] |
|
Service Broker |
Service Broker は、非同期の疎結合アプリケーションを構築する際に役立ちます。これらのアプリケーションでは、複数の独立したコンポーネントの働きが組み合わされて、1 つのタスクが遂行されます。これらのアプリケーション コンポーネントは、タスクの実行に必要な情報が含まれたメッセージを交換します。Service Broker では、メモリに保存できない既存のダイアログ コンテキストを保持するために、tempdb を明示的に使用します。サイズは、ダイアログあたり約 1 KB です。 さらに、Service Broker はタイマー イベントに使用される作業テーブルやバックグラウンド送信メッセージ交換など、クエリ実行のコンテキストでオブジェクトのキャッシュによって、tempdb を暗黙的に使用します。 データベース メール、イベント通知、およびクエリ通知では、Service Broker を暗黙的に使用します。 |
|
ストアド プロシージャ |
ストアド プロシージャでは、グローバル一時テーブルやローカル一時テーブルとそれらのインデックス、変数、パラメーターなどのユーザー オブジェクトを作成できます。ストアド プロシージャ内の一時オブジェクトをキャッシュして、これらのオブジェクトを削除および作成する操作を最適化できます。この動作により、必要な tempdb のディスク領域が増加します。一時オブジェクトごとに最大で 9 つのページが、再使用するために格納されます。この表の「一時テーブルと table 変数」を参照してください。 |
|
一時テーブルと table 変数
|
一時テーブルと table 変数は、tempdb に格納されます。一時テーブル オブジェクトに必要なディスク領域は、以前のバージョンの SQL Server と同じです。一時テーブルのサイズを推定する方法は、標準のテーブルのサイズを推定する方法と同じです。詳細については、「テーブル サイズの見積もり」を参照してください。 table 変数は、ローカル変数のように機能します。table 変数は table 型の変数で、主に、テーブル値関数の結果セットとして返される行のセットの一時的な格納場所として使用されます。table 変数を保持するために必要なディスク領域は、宣言された変数のサイズと、変数に格納された値によって決まります。 次の条件を満たす場合にローカル一時テーブルおよび変数がキャッシュされます。
一時テーブルまたは table 変数がキャッシュされると、その目的が終了したときに一時オブジェクトは削除されません。代わりに、一時オブジェクトが切り捨てられます。最高で 9 つのページが格納され、次回呼び出し元オブジェクトが実行されたときに再使用されます。キャッシュを使用することで、オブジェクトを削除および作成する操作を非常に高速に実行でき、ページ割り当ての競合が減少します。 パフォーマンスを最適化するために、キャッシュされたローカル一時テーブルまたは tempdb 内の table 変数に必要なディスク領域を、次の式を使用して計算してください。 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure |
|
トリガー |
AFTER トリガーで使用される inserted テーブルと deleted テーブルが tempdb に作成されます。つまり、トリガーにより更新または削除される行がバージョン管理されます。この対象には、トリガーを起動したステートメントによって変更されるすべての行が含まれます。トリガーによって挿入される行は、バージョン管理されません。 INSTEAD OF トリガーは、クエリと同様の方法で tempdb を使用します。INSTEAD OF トリガーに使用されるディスク領域は、以前のバージョンの SQL Server と同じです。この表の「クエリ」を参照してください。 トリガーを有効にしてデータを一括読み込みする場合、削除または更新された各行のコピーがバージョン ストアに追加されます。 |
|
ユーザー定義関数 |
ユーザー定義関数では、グローバル テーブルやローカル テーブルとそれらのインデックス、変数、パラメーターなどの一時的なユーザー オブジェクトを作成できます。たとえば、テーブル値関数により返されるテーブルは、tempdb に格納されます。 スカラー関数およびテーブル値関数のパラメーターおよび戻り値に使用可能なデータ型には、LOB データ型のほとんどが含まれます。たとえば、戻り値の型は xml または varchar(max) となります。この表の「ラージ オブジェクト (LOB) データ型の変数とパラメーター」を参照してください。 テーブル値ユーザー定義関数内の一時オブジェクトをキャッシュして、これらのオブジェクトを削除および作成する操作を最適化できます。この表の「一時テーブルと table 変数」を参照してください。 |
|
XML |
xml 型の変数とパラメーターは、最大 2 GB です。これらの変数とパラメーターは、値が小さい場合はメイン メモリをストレージとして使用します。ただし、大きい値は tempdb に格納されます。この表の「ラージ オブジェクト (LOB) データ型の変数とパラメーター」を参照してください。 sp_xml_preparedocument システム ストアド プロシージャは、tempdb に作業テーブルを作成します。MSXML パーサーは、この作業テーブルを使用して、解析された XML ドキュメントを格納します。tempdb に必要なディスク領域は、ストアド プロシージャが実行される際に指定された XML ドキュメントのサイズにほぼ比例します。 |
SQL Server へアップグレードする際のキャパシティ プランニング
実稼働環境での tempdb の適切なサイズを判断するには、多くの要因が関係します。このトピックで前述されているように、これらの要因には既存のワークロードや使用されている SQL Server の機能などがあります。SQL Server のテスト環境で次のタスクを実行して、既存のワークロードを分析することをお勧めします。
tempdb に自動拡張を設定する。
個々のクエリまたはワークロード トレース ファイルを実行し、tempdb 領域の使用を監視する。
インデックスの再構築などのインデックス メンテナンス操作を実行し、tempdb 領域を監視する。
前の手順の使用領域値を使用してワークロード全体の使用量を予測し、予測される同時処理に合わせてこの値を調整し、それに応じて tempdb のサイズを設定する。
tempdb 領域の監視の詳細については、「tempdb のディスク領域の不足に関するトラブルシューティング」を参照してください。インデックス操作時の tempdb 使用量推定の詳細については、「インデックスのディスク領域の例」を参照してください。
実稼働環境に合わせた tempdb の構成
tempdb のパフォーマンスを最適にするためには、「tempdb のパフォーマンスの最適化」で説明されているガイドラインと推奨事項に従ってください。
tempdb の使用状況を監視する方法
tempdb のディスク領域が不足すると、SQL Server の実稼働環境で重大な障害が発生したり、実行中のアプリケーションの操作を完了できなくなる場合があります。sys.dm_db_file_space_usage 動的管理ビューを使用して、上記の機能により使用される tempdb ファイルのディスク領域を監視できます。また、tempdb のページの割り当てや割り当て解除の状態をセッション レベルまたはタスク レベルで監視するには、sys.dm_db_session_space_usage 動的管理ビューと sys.dm_db_task_space_usage 動的管理ビューを使用できます。これらのビューを使用すると、tempdb のディスク領域を大量に使用している大きなクエリ、一時テーブル、またはテーブル変数を特定できます。さらに、tempdb で使用可能な空き領域と tempdb を使用しているリソースの監視に使用できるいくつかのカウンターもあります。詳細については、「tempdb のディスク領域の不足に関するトラブルシューティング」を参照してください。