tempdb データベース

[アーティクル]
04/18/2022

適用対象:SQL Server (サポートされているすべてのバージョン) Azure SQL Database

この記事では、tempdbSQL Server、Azure SQL Database、または Azure SQL Managed Instance のインスタンスに接続しているすべてのユーザーが使用できるグローバルリソースであるシステムデータベースについて説明します。

概要

tempdb システムデータベースは、次のものを保持するグローバルリソースです。

明示的に作成された一時的な "ユーザーオブジェクト"。グローバルまたはローカルな一時テーブルおよびインデックス、一時ストアドプロシージャ、テーブル変数、テーブル値関数で返されるテーブル、カーソルなどが含まれます。
データベースエンジンによって作成された "内部オブジェクト"。それには以下が含まれます。
- スプール、カーソル、並べ替え、および一時的なラージオブジェクト (LOB) 記憶域の中間結果を格納する作業テーブル。
- ハッシュ結合操作またはハッシュ集計操作用の作業ファイル
- インデックスの作成または再構築などの操作 (SORT_IN_TEMPDB が指定されている場合) や、GROUP BY、ORDER BY、UNION などの特定のクエリにおける、並べ替えの中間結果。
各内部オブジェクトでは、少なくとも 9 つのページが使用されます (IAM ページと 8 ページ分のエクステント)。ページとエクステントの詳細については、「ページとエクステント」を参照してください。
"バージョンストア"。これは行のバージョン管理のための機能をサポートするデータ行が保持されるデータページのコレクションです。共通バージョンストアとオンラインインデックスビルドバージョンストアの 2 種類があります。バージョンストアに保持される内容:
- 行バージョン管理分離トランザクションまたはスナップショット分離トランザクションを通して READ COMMITTED を使用するデータベース内のデータ変更トランザクションによって生成される行バージョン。
- オンラインインデックス操作、複数のアクティブな結果セット (MARS)、AFTER トリガーなどの機能に対するデータ変更トランザクションによって生成される行バージョン。

トランザクションをロールバックできるように、tempdb での操作のログ記録は最小限に抑えられます。 tempdbは、データベースが起動SQL Serverに作成され、システムは常にデータベースのクリーンコピーで開始されます。一時テーブルと一時ストアドプロシージャは、切断時に自動的に削除され、システムのシャットダウン時にアクティブな接続はありません。

tempdbのセッションから別のセッションに保存SQL Serverはありません。 tempdb では、バックアップおよび復元の操作は実行できません。

SQL Server での tempdb の物理プロパティ

次の表は、SQL Server での tempdb のデータファイルとログファイルの初期構成値の一覧です。値は、model データベースの既定値に基づいています。これらのファイルのサイズは、各ファイルのエディションによって若干異なる場合SQL Server。

ファイル	論理名	物理名	初期サイズ	ファイル拡張
プライマリデータ	tempdev	tempdb.mdf	8 MB	ディスクがいっぱいになるまで 64 MB ずつ自動拡張
セカンダリデータファイル	temp#	tempdb_mssql_#.ndf	8 MB	ディスクがいっぱいになるまで 64 MB ずつ自動拡張
ログ	templog	templog.ldf	8 MB	最大 2 TB まで 64 MB ずつ自動拡張

セカンダリデータファイルの数は、コンピューター上の (論理) プロセッサの数に依存します。一般的なルールとして、論理プロセッサの数が 8 以下の場合、論理プロセッサと同じ数のデータファイルを使用します。論理プロセッサの数が 8 より多い場合は、8 つのデータファイルが使用されます。そのとき、競合が続く場合は、競合が許容できるレベルに低下するまでデータファイルの数を 4 の倍数分ずつ増やすか、ワークロードまたはコードを変更します。

Note

データファイルの数の既定値は、 KB 2154845の一般的なガイドラインに基づいています。

Note

tempdb の現在のサイズと拡張パラメーターを確認するには、ビュー tempdb.sys.database_files でクエリを実行します。

SQL Server の tempdb のデータファイルとログファイルの移動

データファイルとログファイルを tempdb 移動するには、「システムデータベースを移動 tempdb。

SQL Server での tempdb のデータベースオプション

tempdb データベースの各データベースオプションの既定値とそのオプションを変更できるかどうかを次の表に示します。これらのオプションの現在の設定を表示するには、 sys.databases カタログビューを使用します。

データベースオプション	既定値	変更可否
ALLOW_SNAPSHOT_ISOLATION	OFF	はい
ANSI_NULL_DEFAULT	OFF	はい
ANSI_NULLS	OFF	はい
ANSI_PADDING	OFF	はい
ANSI_WARNINGS	OFF	はい
ARITHABORT	OFF	はい
AUTO_CLOSE	OFF	いいえ
AUTO_CREATE_STATISTICS	ON	はい
AUTO_SHRINK	OFF	いいえ
AUTO_UPDATE_STATISTICS	ON	はい
AUTO_UPDATE_STATISTICS_ASYNC	OFF	はい
CHANGE_TRACKING	OFF	いいえ
CONCAT_NULL_YIELDS_NULL	OFF	はい
CURSOR_CLOSE_ON_COMMIT	OFF	はい
CURSOR_DEFAULT	GLOBAL	はい
データベース可用性オプション	ONLINE MULTI_USER READ_WRITE	いいえいいえいいえ
DATE_CORRELATION_OPTIMIZATION	OFF	はい
DB_CHAINING	ON	いいえ
ENCRYPTION	OFF	いいえ
MIXED_PAGE_ALLOCATION	OFF	いいえ
NUMERIC_ROUNDABORT	OFF	はい
PAGE_VERIFY	SQL Server の新規インストールのチェックサム SQL Server のアップグレードの場合は NONE	はい
PARAMETERIZATION	SIMPLE	はい
QUOTED_IDENTIFIER	OFF	はい
READ_COMMITTED_SNAPSHOT	OFF	いいえ
RECOVERY	SIMPLE	いいえ
RECURSIVE_TRIGGERS	OFF	はい
Service Broker のオプション	ENABLE_BROKER	はい
TRUSTWORTHY	OFF	いいえ

これらのデータベースオプションの説明は、「ALTER DATABASE の SET オプション (Transact-SQL)」を参照してください。

Azure SQL での tempdb

Azure SQL Database での tempdb の動作は、SQL Server、Azure SQL Managed Instance、Azure VM 上の SQL Server の動作とは異なります。

SQL Database での tempdb

Azure SQL Databaseの単一データベースとプールデータベースは、グローバル一時テーブルとグローバル一時ストアドプロシージャをサポートし、データベースレベルを対象とし、に格納されます。グローバル一時テーブルとグローバル一時ストアドプロシージャは、同じデータベース内のすべてのユーザーのセッションで共有されます。他のデータベースからのユーザーセッションは、グローバル一時テーブルにアクセスできません。詳細については、「Database scoped global temporary tables (Azure SQL Database)」(データベーススコープグローバル一時テーブル (Azure SQL Database)) を参照してください。

Azure SQL Database 内の単一データベースとプールされたデータベースの場合、すべてのシステムデータベースの中で、master データベースと tempdb データベースのみがアクセス可能です。詳細については、Azure での論理サーバーの概要に関する記事を参照してください。

Azure SQL Database での tempdb のサイズの詳細については、次を確認してください。

仮想コア購入モデル: 単一データベース、プールされたデータベース
DTU 購入モデル: 単一データベース、プールされたデータベース

SQL Managed Instance での tempdb

Azure SQL Managed Instance では、SQL Server と同じ方法で一時オブジェクトがサポートされます。この場合、すべてのグローバル一時テーブルとグローバル一時ストアドプロシージャは、同じマネージドインスタンス内のすべてのユーザーセッションからアクセスできます。同様に、すべてのシステムデータベースがアクセス可能です。

Azure SQL Managed Instance での tempdb のサイズの詳細については、リソース制限に関する記事を参照してください。

制限事項

tempdb データベースでは、次の操作を実行できません。

ファイルグループの追加。
データベースのバックアップまたは復元。
照合順序の変更。既定の照合順序はサーバーの照合順序です。
データベース所有者の変更。 tempdbtempdbが所有している。
データベーススナップショットの作成。
データベースの削除。
データベースからの guest ユーザーの削除。
変更データキャプチャの有効化。
データベースミラーリングへの参加。
プライマリファイルグループ、プライマリデータファイル、またはログファイルの削除。
データベース名またはプライマリファイルグループ名の変更。
DBCC CHECKALLOC の実行。
DBCC CHECKCATALOG の実行。
データベースの OFFLINE への設定。
データベースまたはプライマリファイルグループの READ_ONLY への設定。

アクセス許可

すべてのユーザーが tempdb 内に一時オブジェクトを作成できます。ユーザーは追加の権限を付与されない限り、自分で作成したオブジェクトにしかアクセスできません。 tempdb に対する接続アクセス許可を取り消して、ユーザーが tempdb を使用できないようにすることができます。一部のルーチン操作で tempdb を使用する必要があるため、それはお勧めしません。

SQL Server の tempdb のパフォーマンスの最適化

tempdb データベースのサイズと物理的な配置場所は、システムのパフォーマンスに影響を与えることがあります。たとえば、に tempdb 定義されているサイズが小さすぎる場合は、SQL Server のインスタンスを再起動するたびに、ワークロードをサポートするために必要なサイズに自動的に拡張することで、システム処理負荷の一部が増加 tempdb する可能性があります。

可能な場合は、ファイルの瞬時初期化を使用して、データファイルの拡張操作のパフォーマンスを向上させます。

すべての tempdb ファイルに対する領域をあらかじめ割り当てるには、環境における一般的なワークロードに十分に対応できる大きさの値にファイルサイズを設定します。事前に割り当てておけば、tempdb は頻繁に拡張されず、パフォーマンスに影響が出なくなります。 tempdb データベースは、想定外の例外に対してディスク領域が増加するよう、自動拡張が行われるように設定する必要があります。

データファイルは、各ファイルグループ内で同じサイズにする必要があります。 SQL Server では、より多くの空き領域があるファイル内の割り当てを優先するプロポーショナルフィルアルゴリズムが使用されるためです。サイズの等しい複数のデータファイルに tempdb を分割すると、tempdb を使用する操作において効率の高い並列処理を実現できます。

tempdb データベースファイルの拡張単位が小さすぎることのないように、ファイル拡張の増分値を妥当なサイズに設定します。 tempdb に書き込まれるデータ量と比較してファイルの拡張単位が小さすぎると、tempdb を頻繁に拡張することが必要になる場合があります。それにより、パフォーマンスが影響を受けます。

tempdb の現在のサイズと拡張パラメーターを確認するには、次のクエリを使用します。

 SELECT name AS FileName,
    size*1.0/128 AS FileSizeInMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file grows to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

高速な I/O サブシステムに tempdb データベースを配置します。直接アタッチされたディスクが多数ある場合は、ディスクストライピングを使用します。 I/O ボトルネックも発生しているのでない限り、個々の tempdb データファイルまたはそのグループを、異なるディスクまたはスピンドルに配置する必要は必ずしもありません。

ユーザーデータベースによって使用されるものとは異なるディスクに、tempdb データベースを配置します。

SQL Server の tempdb でのパフォーマンスの強化

SQL Server 2016 (13. x) 以降では、 tempdb 次の方法でパフォーマンスがさらに最適化されています。

一時テーブルとテーブル変数はキャッシュされます。キャッシュを使用することで、一時オブジェクトを削除および作成する操作を非常に高速に実行できます。また、キャッシュによって、ページの割り当てやメタデータの競合も減少します。
割り当てページラッチプロトコルが改善され、使用される UP (更新) ラッチの回数が減っています。
tempdb のログ記録オーバーヘッドが減少し、tempdb ログファイルのディスク I/O 帯域幅消費が減少しました。
セットアップによって、新しいインスタンスのインストール中に複数の tempdb データファイルが追加されます。このタスクを実行するには、データベースエンジン構成セクションの新しい UI 入力コントロールとコマンドラインパラメーターを使用します。既定では、セットアップ時に、論理プロセッサ数または 8 のいずれか小さい方と同数の tempdb データファイルが追加されます。
複数の tempdb データファイルがある場合は、拡張設定に応じて、すべてのファイルが同時に同量ずつ自動拡張されます。トレースフラグ 1117 は必須ではなくなりました。
tempdb 内のすべての割り当てで単一エクステントが使用されます。トレースフラグ 1118 は必須ではなくなりました。
プライマリファイルグループの場合、AUTOGROW_ALL_FILES プロパティはオンにされており、プロパティは変更できません。

の tempdb パフォーマンス向上の詳細については、ブログ記事「 tempdb参照してください。

メモリ最適化 tempdb メタデータ

の tempdb メタデータ競合は、従来、SQL Server で実行されている多くのワークロードのスケーラビリティに対するボトルネックでした。 SQL Server 2019 (15. x) では、インメモリデータベース機能ファミリに含まれる新しい機能であるメモリ最適化された tempdb メタデータが導入されています。

この機能により、このボトルネックが実質的に除去され、tempdb の負荷が高いワークロードに新しいレベルのスケーラビリティが提供されます。 SQL Server 2019 (15. x) では、一時テーブルのメタデータの管理に関係するシステムテーブルを、ラッチフリーの非持続的メモリ最適化テーブルに移動できます。

現在、Azure SQL Database または Azure SQL Managed Instance では、メモリ最適化 tempdb メタデータ機能は使用できません。

メモリ最適化 tempdb メタデータを使用する方法とそのタイミングの概要については、この 7 分間のビデオをご覧ください。

メモリ最適化 tempdb メタデータの構成と使用

この新しい機能にオプトインするには、次のスクリプトを使用します。

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

この構成の変更を有効にするには、サービスの再起動が必要です。

次の T-SQL コマンドを使用して、tempdb がメモリ最適化かどうかを確認できます。

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

メモリ最適化 tempdb されたメタデータを有効にした後、何らかの理由でサーバーの起動に失敗した場合は、 -fスタートアップオプションを使用してtempdbで SQL Server インスタンスを起動することで、この機能をバイパスできます。その後、この機能を無効にして、通常モードで SQL Server を再起動できます。

サーバーをメモリ不足の状態から保護するために、tempdbにバインド tempdb することができます。これは、リソースプールをデータベースにバインドするために通常実行する手順の代わりに ALTER SERVER コマンドを使用して行います。

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');

メモリ最適化 tempdb メタデータが既に有効になっている場合でも、この変更を有効にするには再起動も必要です。

メモリ最適化 tempdb メタデータの制限

機能のオンとオフの切り替えは、動的ではありません。 tempdb の構造を根本的に変更する必要があるため、この機能を有効または無効にするには再起動が必要です。
1 つのトランザクションで複数のデータベース内のメモリ最適化テーブルにアクセスすることはできません。ユーザーデータベース内のメモリ最適化テーブルを使用するトランザクションでは、同じトランザクション内で tempdb システムビューにアクセスすることはできません。ユーザーデータベース内のメモリ最適化テーブルと同じトランザクションで tempdb システムビューにアクセスしようとした場合、次のエラーが返されます。
```
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
```
例:
```
BEGIN TRAN;

SELECT *
FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb

INSERT INTO <user database>.<schema>.<mem-optimized table>
VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail

COMMIT TRAN;
```
メモリ最適化テーブルに対するクエリではロックと分離のヒントがサポートされていないため、メモリ最適化 tempdb カタログビューに対するクエリでは、ロックと分離のヒントは適用されません。 SQL Server の他のシステムカタログビューと同様に、システムビューに対するすべてのトランザクションが分離さ READ COMMITTEDREAD COMMITTED SNAPSHOT れます。
メモリ最適化されたメタデータが有効になっている場合、一時テーブルに列ストアインデックスを作成することはできません。
列ストアインデックスでの制限により、メモリ最適化 tempdb メタデータが有効になっている場合は、COLUMNSTORE または COLUMNSTORE_ARCHIVE データ圧縮パラメーターを指定して sp_estimate_data_compression_savings システムストアドプロシージャを使用することはできません。

Note

これらの制限は、tempdb システムビューを参照している場合にのみ適用されます。必要に応じて、ユーザーデータベース内のメモリ最適化テーブルにアクセスするときに、同じトランザクションで一時テーブルを作成できます。

SQL Server での tempdb の容量計画

SQL Server 運用環境での tempdb 適切なサイズの決定は、多くの要因に依存します。前述のように、これらの要因には、既存のワークロードと使用される SQL Server の機能が含まれます。 SQL Server のテスト環境で次のタスクを実行して、既存のワークロードを分析することをお勧めします。

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

tempdb の使用の監視

で tempdb ディスク領域が不足すると、SQL Server 運用環境で大幅な中断が発生する可能性があります。また、実行中のアプリケーションが操作を完了できなくなる場合もあります。 Sys.dm_db_file_space_usage動的管理ビューを使用して、ファイルで使用されているディスク領域を監視できます。

 -- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

セッションレベルまたはタスクレベルででの tempdb ページの割り当てまたは割り当て解除のアクティビティを監視するには、 tempdb を使用し、動的管理ビューを sys.dm_db_task_space_usage します。これらのビューを使用すると、tempdb のディスク領域を大量に使用している大きなクエリ、一時テーブル、またはテーブル変数を特定できます。また、いくつかのカウンターを使用して、tempdb で使用できる空き領域と、tempdb を使用しているリソースを監視することもできます。

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

tempdb データベース

概要

SQL Server での tempdb の物理プロパティ

SQL Server の tempdb のデータ ファイルとログ ファイルの移動

SQL Server での tempdb のデータベース オプション

Azure SQL での tempdb

SQL Database での tempdb

SQL Managed Instance での tempdb

制限事項

アクセス許可

SQL Server の tempdb のパフォーマンスの最適化

SQL Server の tempdb でのパフォーマンスの強化

メモリ最適化 tempdb メタデータ

メモリ最適化 tempdb メタデータの構成と使用

メモリ最適化 tempdb メタデータの制限

SQL Server での tempdb の容量計画

tempdb の使用の監視

次のステップ

SQL Server の tempdb のデータファイルとログファイルの移動

SQL Server での tempdb のデータベースオプション