tempdb データベース

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

この記事では、tempdb システム データベースについて説明します。これは、 SQL 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 のエディションによって多少異なる場合があります。

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

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

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

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

tempdb のデータ ファイルとログ ファイルを移動するには、「システム データベースの移動」を参照してください。

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

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

これらのデータベース オプションの説明は、「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 の単一データベースとプールされたデータベースでは、データベース レベルにスコープされたグローバル一時テーブルとグローバル一時ストアド プロシージャがサポートされ、tempdb に保存されます。 グローバル一時テーブルとグローバル一時ストアド プロシージャは、同じデータベース内のすべてのユーザーのセッションで共有されます。 他のデータベースからのユーザー セッションは、グローバル一時テーブルにアクセスできません。 詳細については、「Database scoped global temporary tables (Azure SQL Database)」(データベース スコープ グローバル一時テーブル (Azure SQL Database)) を参照してください。

単一データベースの場合、論理サーバー上の各単一データベースには独自の tempdb があります。 エラスティック プール内では、tempdb は同じプール内のすべてのデータベースの共有リソースですが、1 つのデータベースで作成される一時オブジェクトは、プール内の他のデータベースには表示されません。

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

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

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

SQL Managed Instance での tempdb

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

ファイルの数、ファイルの tempdb 増加の増分、および最大サイズを設定できます。 Azure SQL Managed Instance のtempdb 設定の構成に関する詳細については、「Azure SQL Managed Instance のtempdb 設定を構成する」を参照してください。

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

制限

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

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

アクセス許可

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

SQL Server でクエリのパフォーマンスを最適化する

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

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

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

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

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

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

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.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_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

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

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

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

SQL Server 2016 (13.x) で導入されています。

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

SQL Server 2017 (14.x) で導入されています。

• SQL セットアップ エクスペリエンスにより、初期 tempdb ファイル割り当てのガイダンスが向上します。 SQL セットアップでは、インスタンスの起動遅延を防ぐため、初期ファイル サイズが 1 GB を超える値に設定されている場合、およびファイルの瞬時初期化が有効になっていない場合に警告が表示されます。
• SQL Server 2017 では、データベースごとのバージョン ストアの使用状況を追跡するために、DMV sys.dm_tran_version_store_space_usage が新たに導入されました。 この新しい DMV は、データベースごとのバージョン ストアの使用量の要件に基づいて tempdb サイズ設定を事前に計画できる tempdb DBA のバージョン ストアの使用状況の監視に役立ちます。
• アダプティブ結合やメモリ許可フィードバックなどの新しい インテリジェントなクエリ処理 機能により、クエリの連続実行時のメモリ スピルが削減され、不要な tempdb 使用率を削減します。

SQL Server 2019 (15.x) で導入されています。

• SQL Server 2019 (15.x) 以降、SQL Server はディスクのスループットを最大にするため、 FILE_FLAG_WRITE_THROUGH のファイルを開くときに tempdb オプションを使用しません。 tempdb はSQL Server の起動時に再作成されるため、他のシステム データベースとユーザー データベースのデータ整合性を確保するためにも、これらのオプションは必要ありません。 FILE_FLAG_WRITE_THROUGH の詳細については、「SQL Serverでのデータの信頼性を拡張するログ記録アルゴリズムとデータ ストレージ アルゴリズム」を参照してください。
• メモリ最適化 TempDB メタデータにより、tempdb で PAGELATCH 待機のボトルネックが除去され、新たなレベルのスケーラビリティが実現されます。 詳細については、ビデオ デモ「メモリ最適化 TempDB メタデータ：方法 (およびタイミング) 」をご覧ください。 詳細については、メモリ最適化 tempdb メタデータの監視とトラブルシューティングに関する記事を参照してください。
• ページ空き領域 (PFS) ページの同時更新により、すべてのデータベースでのパッチ ラッチの競合が軽減されます。これは、 tempdb で最もよく見られる問題です。 この機能強化により、PFS の更新でのコンカレンシー管理方法が変更され、排他的ラッチではなく共有ラッチで更新できるようになります。 この動作は、SQL Server 2019 (15.x) 以降のすべてのデータベース (TempDB など) で、既定でオンになります。 PFS ページの詳細については、「内部のしくみ：GAM、SGAM、PFS ページ」を参照してください。
• 既定では、Linux 上に SQL Server を新しくインストールすると、論理コアの数に基づいて複数の tempdb データ ファイルが作成されます (最大で 8 個のデータ ファイル)。 これは、マイナー バージョンまたはメジャー バージョンのインプレース アップグレードには適用されません。 各 tempdb ファイルは 8 MB で、64 MB まで自動拡張されます。 この動作は、Windows への SQL Server の既定のインストールに似ています。

SQL Server 2022 (16.x) で導入されています。

• SQL Server 2022 (16.x) では 、システム ページ ラッチのコンカレンシーが強化され、スケーラビリティが向上しました。 グローバル アロケーション マップ (GAM) ページと共有グローバル アロケーション マップ (SGAM) ページへの同時更新により、データ ページとエクステントの割り当てまたは割り当て解除中のページ ラッチの競合が軽減されます。 これらの機能強化は、すべてのユーザー データベースに適用され、tempdb の負荷の高いワークロードに特に役立ちます。 GAM ページと SGAM ページの詳細については、「内部のしくみ： GAM、SGAM、および PFS ページ」を参照してください。 詳細については、「システム ページ ラッチ コンカレンシーの強化(Ep. 6)」| 公開されているデータ」を参照してください。

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

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

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

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

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

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

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

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

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

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

サーバーを潜在的なメモリ不足の状態から保護するために、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 COMMITTED (または、このケースでは READ COMMITTED SNAPSHOT )の分離になります。

• メモリ最適化 TempDB メタデータが有効になっている場合、一時テーブルに列ストア インデックスを作成することはできません。

• 列ストア インデックスでの制限により、メモリ最適化 TempDB メタデータが有効になっている場合は、COLUMNSTORE または COLUMNSTORE_ARCHIVE データ圧縮パラメーターを指定して sp_estimate_data_compression_savings システム ストアド プロシージャを使用することはできません。

• システム ストアド プロシージャを使用すると、ガベージ コレクションの対象となるメモリ内データの削除された行に関連するメモリをインメモリ エンジンに手動で解放させることができます。 これは、特定のメモリ最適化 tempdb メタデータ (HkTempDB) のメモリ不足のエラーのトラブルシューティングに役立ちます。 詳細については、「ys.sp_xtp_force_gc (Transact-SQL)」を参照してください。

SQL Server での tempdb の容量計画

SQL Server 運用環境での tempdb の適切なサイズを判断するには、多くの要因が関係します。 前に説明したように、これらの要因には既存のワークロードや使用されている SQL Server の機能などがあります。

SQL Server のテスト環境で次のタスクを実行して、既存のワークロードを分析することをお勧めします。

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

tempdb の使用の監視

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

たとえば、次の 4 つのサンプル スクリプトでは、tempdb の空き容量、バージョン ストアの空き容量、内部オブジェクトの使用容量、およびユーザー オブジェクトの使用容量を検索します。

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

次のスクリプトを使用して、実行中のタスクと完了したタスクの両方において、現在のセッションで内部オブジェクトによって　tempdb 使用されている領域を検索します。

-- 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;

関連するコンテンツ

• インデックスの SORT_IN_TEMPDB オプション
• システム データベース
• sys.databases
• sys.master_files
• データベース ファイルの移動