tempdb に使用するディスク領域の計画

  • [アーティクル]

更新 : 2006 年 4 月 14 日

このトピックでは、tempdb システム データベースに対して SQL Server 2005 で加えられた変更について説明し、tempdb に必要なディスク領域の適切な量を決定するためのガイドラインを示します。さらに、このトピックでは実稼働環境でパフォーマンスを最適にするために tempdb を構成する方法の推奨事項と、tempdb 領域の使用状況を監視する方法について説明します。

SQL Server 2005 では、以前のバージョンの SQL Server より多くのディスク領域が tempdb で必要となります。これは、次の変更が加えられたためです。

  • SQL Server 2005 の一部の新機能が、tempdb を使用します。
  • 以前のバージョンの SQL Server で tempdb を使用する一部の機能には、SQL Server 2005 ではそれより多くの tempdb ディスク領域が必要なことがあります。
  • 以前のバージョンの SQL Server では tempdb を使用しない一部の機能が、SQL Server 2005 では tempdb を使用します。

これらの変更のため、SQL Server 2005 にアップグレードする際は十分な tempdb ディスク領域を確保して、現在の実稼働ワークロードを処理できるだけでなく、SQL Server の機能で tempdb を使用するための追加の領域要件が満たされるようにしてください。

tempdb の使用方法

tempdb システム データベースは、SQL Server のインスタンスに接続しているすべてのユーザーが使用できるグローバル リソースです。tempdb データベースはユーザー オブジェクト、内部オブジェクト、およびバージョン ストアの格納に使用されます。

ユーザー オブジェクト

ユーザー オブジェクトは、ユーザーによって明示的に作成されます。これらのオブジェクトは、ユーザー セッションのスコープ内、またはオブジェクトが作成されるルーチンのスコープ内に入れることができます。ルーチンは、ストアド プロシージャ、トリガ、またはユーザー定義関数です。ユーザー オブジェクトは、次のいずれかです。

  • ユーザー定義テーブルとインデックス
  • システム テーブルとインデックス
  • グローバル一時テーブルとインデックス
  • ローカル一時テーブルとインデックス
  • テーブル変数
  • テーブル値関数で返されるテーブル

内部オブジェクト

内部オブジェクトは、SQL Server ステートメントを処理するために必要に応じて SQL Server データベース エンジンにより作成されます。内部オブジェクトは、ステートメントのスコープ内で作成および削除されます。内部オブジェクトは、次のいずれかです。

  • カーソルまたはスプール操作用の作業テーブルと、一時的なラージ オブジェクト (LOB) 格納領域
  • ハッシュ結合操作またはハッシュ集計操作用の作業ファイル
  • インデックスの作成または再構築などの操作 (SORT_IN_TEMPDB を指定した場合) や、GROUP BY、ORDER BY、または UNION クエリにおける並べ替えの中間結果

各内部オブジェクトでは、少なくとも 9 つのページが使用されます (1 つの IAM ページと 8 ページ分のエクステント)。ページとエクステントの詳細については、「ページとエクステント」を参照してください。

バージョン ストア

バージョン ストアは、行のバージョン管理を使用する機能のサポートに必要なデータ行を保持するデータ ページのコレクションです。SQL Server 2005 には、共通バージョン ストアとオンライン インデックス構築用のバージョン ストアの 2 つのバージョン ストアがあります。バージョン ストアには、次の内容が保持されます。

  • 行のバージョン管理を伴う READ COMMITTED 分離レベルまたはスナップショット分離レベルを使用しているデータベースで、データ変更トランザクションによって生成される行バージョン
  • オンライン インデックス操作、複数のアクティブな結果セット (MARS)、および AFTER トリガなどの機能に対してデータ変更トランザクションによって生成される行バージョン

次の表に、tempdb にユーザー オブジェクト、内部オブジェクト、または行バージョンを作成する SQL Server の機能を示します。可能な場合は、ディスク領域使用量の推定方法を説明します。

機能 tempdb の用途 追加情報

トリガが有効な場合の一括読み込み操作

SQL Server 2005 では、トリガが有効になっていると一括インポートの最適化を使用できます。SQL Server 2005 は、トランザクションを更新または削除するトリガに対して、行バージョン管理を使用します。削除または更新された各行のコピーが、バージョン ストアに追加されます。この表の「トリガ」を参照してください。

一括インポートのパフォーマンスの最適化

共通テーブル式のクエリ

共通テーブル式は、単一の SELECT、INSERT、UPDATE、DELETE、CREATE VIEW の各ステートメントの実行スコープ内で定義される一時結果セットと考えることができます。

共通テーブル式のクエリのクエリ プランがスプール操作を使用してクエリの中間結果を保存する場合、この操作をサポートするために、データベース エンジンによって tempdb に作業テーブルが作成されます。

共通テーブル式の使用

WITH common_table_expression (Transact-SQL)

カーソル

キーセット ドリブン カーソルと静的カーソルは、tempdb に作成された作業テーブルを使用します。キーセット ドリブン カーソルは、この作業テーブルを使用して、カーソル内の行を識別するキーのセットを格納します。静的カーソルは、作業テーブルを使用して、そのカーソルの結果セット全体を格納します。

カーソルに使用されるディスク領域は、選択したクエリ プランによって異なることがあります。クエリ プランが以前のバージョンの SQL Server と同じ場合、使用されるディスク領域はほぼ同じになります。

カーソルの種類の選択について

データベース メール

この表の「Service Broker」を参照してください。

データベース メール

DBCC CHECKDB

DBCC CHECKDB は、tempdb 作業テーブルを使用して、中間結果の保持と並べ替え操作を行います。

DBCC CHECKDB に使用されるディスク領域は、DBCC CHECK 操作の次の変更点のために増加しました。

  • 導入された論理チェックの対象範囲が広がった。
  • チェック対象の、基になる構造の一部が複雑になった。
  • SQL Server 2005 の新機能を含めるために多数の新しいチェックが導入された。

操作を行うために必要な tempdb ディスク領域を判断するには、DBCC CHECKDB WITH ESTIMATE_ONLY を実行します。

DBCC CHECKDB (Transact-SQL)

DBCC CHECKDB のパフォーマンスの最適化

イベント通知

この表の「Service Broker」を参照してください。

イベント通知について

インデックス

インデックスを作成または再構築し (オフラインまたはオンライン)、SORT_IN_TEMPDB オプションを ON に設定する場合は、インデックスの構築に使用する中間の並べ替え結果の格納場所としてデータベース エンジンが tempdb を使用するように指定できます。SORT_IN_TEMPDB が指定されていて、並べ替えが必要な場合、tempdb には最も大きいインデックスを保持するのに十分なディスク領域に加えて、index create memory オプションの値に等しいディスク領域が必要です。詳細については、「インデックスのディスク領域の例」を参照してください。

SQL Server 2005 の機能を使用しないオフラインのインデックス操作に使用されるディスク領域は、以前のバージョンの SQL Server と同じです。

SQL Server 2005 における tempdb の用途の変更

SQL Server 2005 では、テーブルとインデックスをパーティション分割できます。パーティション インデックスでは、SORT_IN_TEMPDB インデックス オプションが指定されていて、ベース テーブルにインデックスが固定されている場合、最も大きいパーティションでの中間の並べ替え実行結果を保持するのに十分な領域が tempdb に必要です。インデックスが固定されていない場合、すべてのパーティションの中間の並べ替え実行結果を保持するのに十分な領域が tempdb に必要です。詳細については、「パーティション インデックスの専用ガイドライン」を参照してください。

オンライン インデックス操作では、行のバージョン管理を使用して、他のトランザクションによる変更がインデックス操作に影響を与えないようにしています。行のバージョン管理により、読み取った行の共有ロックを要求する必要がなくなります。オンライン インデックス操作と同時にユーザーの更新操作と削除操作を実行するには、tempdb にバージョン レコード用の領域が必要になります。オンライン インデックス操作で SORT_IN_TEMPDB が使用され、並べ替えが必要な場合、tempdb には前述の並べ替え中間結果に使用する追加のディスク領域も必要です。クラスタ化インデックスを作成、削除、または再構築するオンライン インデックス操作では、一時マッピング インデックスを構築および保持するために追加のディスク領域も必要になります。詳細については、「インデックス DDL 操作に必要なディスク領域」を参照してください。

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)

SQL Server 2005 では、1 つの接続で複数のアクティブな結果セットが発生することがあります。これを通常 MARS と呼びます。アクティブな結果セットが存在するときに、MARS セッションでデータ変更ステートメント (INSERT、UPDATE、DELETE など) が実行された場合、その変更ステートメントの影響を受けた行は tempdb 内のバージョン ストアに格納されます。この表の「行のバージョン管理」を参照してください。

複数のアクティブな結果セット (MARS) の使用

クエリ通知

この表の「Service Broker」を参照してください。

クエリ通知の使用

クエリ

SELECT、INSERT、UPDATE、および DELETE の各ステートメントが含まれるクエリは、内部オブジェクトを使用して、ハッシュ結合、ハッシュ集計、または並べ替えを行うための中間結果を格納できます。

SQL Server 2005 では、クエリの実行プランがキャッシュされている場合、プランに必要な作業テーブルがキャッシュされます。作業テーブルがキャッシュされると、テーブルは切り捨てられ、再使用するために 9 つのページがキャッシュに残ります。これにより、クエリを次回実行する際のパフォーマンスが向上します。システムのメモリが少ない場合、データベース エンジンは実行プランを削除して、関連する作業テーブルを削除できます。

実行プランのキャッシュと再利用

行のバージョン管理

行のバージョン管理は、次の機能をサポートするために使用される SQL Server 2005 の一般的なフレームワークです。

  • トリガ
  • 複数のアクティブな結果セット (MARS)
  • ONLINE オプションを指定するインデックス操作
  • 行のバージョン管理ベースのトランザクション分離レベル
    • 行のバージョン管理を使用してステートメントレベルの読み取りの一貫性を保証する新しい READ COMMITTED 分離レベルの実装
    • トランザクションレベルの読み取りの一貫性を保証するスナップショット分離レベル

アクティブなトランザクションで行バージョンにアクセスする必要がある限り、その行バージョンは 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 の領域の量を推定するには、まずアクティブなトランザクションがすべての変更をバージョン ストアに保持することを考慮する必要があります。これは、後で開始するスナップショット トランザクションが古いバージョンにアクセス可能であることを意味します。さらに、アクティブなスナップショット トランザクションがある場合、スナップショットが開始したときにアクティブになるトランザクションにより生成されたすべてのバージョン ストア データも保持する必要があります。

次に基本的な式を示します。

[バージョン ストアのサイズ] = 2 *

[毎分生成されるバージョン ストア データ] *

[トランザクションの最長実行時間 (分)]

行のバージョン管理に基づく分離レベルについて

行のバージョン管理用リソースの使用状況

Service Broker

Service Broker は、非同期の疎結合アプリケーションを構築する際に役立ちます。これらのアプリケーションでは、複数の独立したコンポーネントの働きが組み合わされて、1 つのタスクが遂行されます。これらのアプリケーション コンポーネントは、タスクの実行に必要な情報が含まれたメッセージを交換します。Service Broker では、メモリに保存できない既存のダイアログ コンテキストを保持するために、tempdb を明示的に使用します。サイズは、ダイアログあたり約 1 KB です。

さらに、Service Broker はタイマ イベントに使用される作業テーブルやバックグラウンド送信メッセージ交換など、クエリ実行のコンテキストでオブジェクトのキャッシュによって、tempdb を暗黙的に使用します。

データベース メール、イベント通知、およびクエリ通知では、Service Broker を暗黙的に使用します。

Service Broker の概要

ストアド プロシージャ

ストアド プロシージャでは、グローバル一時テーブルやローカル一時テーブルとそれらのインデックス、変数、パラメータなどのユーザー オブジェクトを作成できます。SQL Server 2005 では、ストアド プロシージャ内の一時オブジェクトをキャッシュして、これらのオブジェクトを削除および作成する操作を最適化できます。この動作により、必要な tempdb のディスク領域が増加します。一時オブジェクトごとに最大で 9 つのページが、再使用するために格納されます。この表の「一時テーブルと table 変数」を参照してください。

ストアド プロシージャの作成 (データベース エンジン)

一時テーブルと table 変数

  • ユーザー定義テーブルとインデックス
  • システム テーブルとインデックス
  • グローバル一時テーブルとインデックス
  • ローカル一時テーブルとインデックス
  • table 変数
  • テーブル値関数で返されるテーブル

一時テーブルと table 変数は、tempdb に格納されます。一時テーブル オブジェクトに必要なディスク領域は、以前のバージョンの SQL Server と同じです。一時テーブルのサイズを推定する方法は、標準のテーブルのサイズを推定する方法と同じです。詳細については、「テーブル サイズの見積もり」を参照してください。

table 変数は、ローカル変数のように機能します。table 変数は table 型の変数で、主に、テーブル値関数の結果セットとして返される行のセットの一時的な格納場所として使用されます。table 変数を保持するために必要なディスク領域は、宣言された変数のサイズと、変数に格納された値によって決まります。

SQL Server 2005 では、次の条件を満たす場合にローカル一時テーブルおよび変数がキャッシュされます。

  • 名前付き制約が作成されていない。
  • 一時テーブルが作成された後、CREATE INDEX ステートメントまたは CREATE STATISTICS ステートメントなど、テーブルに影響を与えるデータ定義言語 (DDL) ステートメントが実行されていない。
  • 一時オブジェクトが、sp_executesql N'create table #t(a int)' などの動的 SQL を使用して作成されていない。
  • 一時オブジェクトがストアド プロシージャ、トリガ、ユーザー定義関数などの他のオブジェクト内で作成されたか、ユーザー定義テーブル値関数の返されたテーブルである。

一時テーブルまたは table 変数がキャッシュされると、その目的が終了したときに一時オブジェクトは削除されません。代わりに、一時オブジェクトが切り捨てられます。最高で 9 つのページが格納され、次回呼び出し元オブジェクトが実行されたときに再使用されます。キャッシュを使用することで、オブジェクトを削除および作成する操作を非常に高速に実行でき、ページ割り当ての競合が減少します。

パフォーマンスを最適化するために、キャッシュされたローカル一時テーブルまたは tempdb 内の table 変数に必要なディスク領域を、次の式を使用して計算してください。

一時テーブルごとに 9 つのページ

* プロシージャごとの一時テーブルの平均数

* プロシージャの同時実行の最大数

CREATE TABLE (Transact-SQL)

変数とパラメータの使用 (データベース エンジン)

DECLARE @local_variable (Transact-SQL)

トリガ

以前のバージョンの SQL Server では、トリガのロジックはログ レコードに基づいており、tempdb は使用されません。SQL Server 2005 では、AFTER トリガで使用される inserted テーブルと deleted テーブルが tempdb に作成されます。つまり、トリガにより更新または削除される行がバージョン管理されます。この対象には、トリガを起動したステートメントによって変更されるすべての行が含まれます。トリガによって挿入される行は、バージョン管理されません。

INSTEAD OF トリガは、クエリと同様の方法で tempdb を使用します。INSTEAD OF トリガに使用されるディスク領域は、以前のバージョンの SQL Server と同じです。この表の「クエリ」を参照してください。

トリガを有効にしてデータを一括読み込みする場合、削除または更新された各行のコピーがバージョン ストアに追加されます。

CREATE TRIGGER (Transact-SQL)

一括インポートのパフォーマンスの最適化

行のバージョン管理用リソースの使用状況

ユーザー定義関数

ユーザー定義関数では、グローバル テーブルやローカル テーブルとそれらのインデックス、変数、パラメータなどの一時的なユーザー オブジェクトを作成できます。たとえば、テーブル値関数により返されるテーブルは、tempdb に格納されます。

SQL Server 2005 では、スカラ関数およびテーブル値関数のパラメータおよび戻り値に使用可能なデータ型には、LOB データ型のほとんどが含まれます。たとえば、戻り値の型は xml または varchar(max) となります。この表の「ラージ オブジェクト (LOB) データ型の変数とパラメータ」を参照してください。

SQL Server 2005 では、テーブル値ユーザー定義関数内の一時オブジェクトをキャッシュして、これらのオブジェクトを削除および作成する操作を最適化できます。この表の「一時テーブルと table 変数」を参照してください。

CREATE FUNCTION (Transact-SQL)

XML

xml 型の変数とパラメータは、最大 2 GB です。これらの変数とパラメータは、値が小さい場合はメイン メモリをストレージとして使用します。ただし、大きい値は tempdb に格納されます。この表の「ラージ オブジェクト (LOB) データ型の変数とパラメータ」を参照してください。

sp_xml_preparedocument システム ストアド プロシージャは、tempdb に作業テーブルを作成します。MSXML パーサーは、この作業テーブルを使用して、解析された XML ドキュメントを格納します。tempdb に必要なディスク領域は、ストアド プロシージャが実行される際に指定された XML ドキュメントのサイズにほぼ比例します。

xml データ型

sp_xml_preparedocument (Transact-SQL)

OPENXML による XML へのクエリ

SQL Server 2005 へアップグレードする際のキャパシティ プランニング

実稼働環境での tempdb の適切なサイズを判断するには、多くの要因が関係します。このトピックで前述されているように、これらの要因には既存のワークロードや使用されている SQL Server の機能などがあります。SQL Server 2005 のテスト環境で次のタスクを実行して、既存のワークロードを分析することをお勧めします。

  1. tempdb に自動拡張を設定する。
  2. 個々のクエリまたはワークロード トレース ファイルを実行し、tempdb 領域の使用を監視する。
  3. インデックスの再構築などのインデックス メンテナンス操作を実行し、tempdb 領域を監視する。
  4. 前の手順の使用領域値を使用してワークロード全体の使用量を予測し、予測される同時処理に合わせてこの値を調整し、それに応じて tempdb のサイズを設定する。

tempdb 領域の監視の詳細については、「tempdb のディスク領域の不足に関するトラブルシューティング」を参照してください。インデックス操作時の tempdb 使用量推定の詳細については、「インデックスのディスク領域の例」を参照してください。

実稼働環境に合わせた tempdb の構成

tempdb のパフォーマンスを最適にするためには、「tempdb のパフォーマンスの最適化」で説明されているガイドラインと推奨事項に従ってください。

tempdb の使用状況を監視する方法

tempdb のディスク領域が不足すると、SQL Server の実稼働環境で重大な障害が発生したり、実行中のアプリケーションの操作を完了できなくなる場合があります。SQL Server 2005 では、sys.dm_db_file_space_usage 動的管理ビューを使用して、上記の機能により使用される tempdb ファイルのディスク領域を監視できます。また、tempdb のページの割り当てや割り当て解除の状態をセッション レベルまたはタスク レベルで監視するには、sys.dm_db_session_space_usage 動的管理ビューと sys.dm_db_task_space_usage 動的管理ビューを使用できます。これらのビューを使用すると、tempdb のディスク領域を大量に使用している大きなクエリ、一時テーブル、またはテーブル変数を特定できます。さらに、tempdb で使用可能な空き領域と tempdb を使用しているリソースの監視に使用できるいくつかのカウンタもあります。詳細については、「tempdb のディスク領域の不足に関するトラブルシューティング」を参照してください。

参照

処理手順

tempdb のディスク領域の不足に関するトラブルシューティング

概念

tempdb データベース
tempdb のパフォーマンスの最適化

その他の技術情報

データベースの最適化
SQL Server 2005 での tempdb の使用 (Working with tempdb in SQL Server 2005)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2006 年 4 月 14 日
更新内容 :
  • 共通テーブル式のクエリで tempdb を使用する状況を明記しました。