【SQL Server】tempdbが肥大化する原因と確認方法｜一時テーブル・ソート・バージョンストア・空き容量対策

【SQL Server】tempdbが肥大化する原因と確認方法｜一時テーブル・ソート・バージョンストア・空き容量対策 SQL

SQL Serverで tempdb が肥大化する、ディスクを圧迫する、「tempdbがいっぱいです」といった状態になることがあります。tempdb は一時テーブルだけでなく、ソート、ハッシュ、スプール、カーソル、行バージョン管理などにも使われるため、原因を分けて確認する必要があります。

この記事では、tempdb の空き容量、ユーザーオブジェクト、内部オブジェクト、バージョンストア、セッション別の使用量を確認するSQLと、肥大化したときの対処・再発防止策を整理します。

先に結論
まず tempdb.sys.dm_db_file_space_usage で、使用量がユーザーオブジェクト、内部オブジェクト、バージョンストアのどれに偏っているかを確認します。次に sys.dm_db_session_space_usage と sys.dm_exec_requests で、どのセッションやSQLが使っているかを追います。原因を見ずにSQL Serverを再起動すれば一時的には空きますが、根本原因が残っていれば再発します。
スポンサーリンク

tempdbがいっぱいになったときの即時対応

緊急時は、空き容量を確保する対応と、増え続けている原因を止める対応を分けます。まず内訳と原因セッションを確認し、業務影響を見ながら対処を選びます。

原因 まずやること 注意点
一時テーブル・テーブル変数が大きい 使用量の大きいセッションとSQLを確認し、処理停止・分割・不要列削減を検討する sleepingセッションが保持している場合もある
内部オブジェクトが大きい 実行計画でSort、Hash Match、Spoolを確認する インデックス不足や統計情報の古さが背景にあることが多い
バージョンストアが大きい 長時間トランザクションを確認し、完了・ROLLBACK・KILLを判断する RCSIやSNAPSHOTを使っているDBでは特に注意
ファイル容量不足 ディスク空き容量を確認し、必要ならtempdbファイルを拡張する OS上でファイルを削除しない
一時的な大量処理 処理完了後に使用量が戻るか確認する 恒常的ならバッチ設計や実行時間帯を見直す

tempdbが使われる主な場面

tempdb はSQL Serverが一時作業領域として使うシステムデータベースです。アプリが明示的に作る一時テーブルだけでなく、SQL Server内部の作業にも使われます。

用途 例 肥大化しやすい状況
ユーザーオブジェクト #temp、##temp、テーブル変数 大量データを一時テーブルに入れる
内部オブジェクト ソート、ハッシュ、スプール、カーソルの作業領域 大きなORDER BY、GROUP BY、Hash Join
バージョンストア スナップショット分離、READ_COMMITTED_SNAPSHOT、オンライン操作 長時間トランザクションがある
一時LOB 大きな文字列やLOBの中間処理 巨大な集計・変換処理
インデックス操作 SORT_IN_TEMPDBを使うインデックス作成・再構築 大きなインデックスメンテナンス

長時間トランザクションやロック待ちがあると、バージョンストアが解放されにくくなることがあります。ロック状況の確認は SQL Serverでテーブルロックを確認するSQL も参考になります。

緊急時に最初の5分で見る順番

tempdb がいっぱいになったときは、まず何が容量を使っているかを分けます。次の順番で確認すると、原因を早く絞れます。

順番 確認すること 使うSQL・見る項目
1 tempdb全体の空き容量 sys.dm_db_file_space_usage
2 ユーザー/内部/バージョンストアの内訳 user_object_reserved_page_count など
3 使用量が多いセッション sys.dm_db_session_space_usage
4 実行中SQLと待機 sys.dm_exec_requests / sys.dm_exec_sql_text
5 バージョンストア原因の長時間トランザクション sys.dm_tran_active_snapshot_database_transactions
6 ファイルサイズ・自動拡張・ディスク空き tempdb.sys.database_files とOS側

tempdbの空き容量と内訳を確認するSQL

まず、tempdb の容量が何に使われているかを確認します。Microsoft Learnでも、sys.dm_db_file_space_usage を使って空き容量、バージョンストア、内部オブジェクト、ユーザーオブジェクトを確認する例が示されています。

tempdb-space-summary.sql
SELECT
    SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
    SUM(version_store_reserved_page_count) * 8.0 / 1024 AS version_store_space_mb,
    SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS internal_object_space_mb,
    SUM(user_object_reserved_page_count) * 8.0 / 1024 AS user_object_space_mb,
    SUM(mixed_extent_page_count) * 8.0 / 1024 AS mixed_extent_space_mb
FROM tempdb.sys.dm_db_file_space_usage;
列 意味 よくある原因
user_object_space_mb 一時テーブルやテーブル変数など 大きな #temp、削除漏れ、バッチ処理
internal_object_space_mb SQL Server内部の作業領域 ソート、ハッシュ、スプール、カーソル
version_store_space_mb 行バージョン管理領域 RCSI、SNAPSHOT、長時間トランザクション
tempdb_free_data_space_mb tempdbデータファイル内の空き 低い場合は逼迫

ファイルごとの使用量を確認する

複数の tempdb データファイルがある場合、ファイルごとの偏りも確認します。特定ファイルだけ極端に使われている場合は、ファイルサイズや自動拡張設定も見直します。

tempdb-file-usage.sql
SELECT
    file_id,
    total_page_count * 8.0 / 1024 AS total_mb,
    allocated_extent_page_count * 8.0 / 1024 AS allocated_mb,
    unallocated_extent_page_count * 8.0 / 1024 AS free_mb,
    version_store_reserved_page_count * 8.0 / 1024 AS version_store_mb,
    internal_object_reserved_page_count * 8.0 / 1024 AS internal_object_mb,
    user_object_reserved_page_count * 8.0 / 1024 AS user_object_mb
FROM tempdb.sys.dm_db_file_space_usage
ORDER BY file_id;

tempdbを使っているセッションを特定する

ユーザーオブジェクトや内部オブジェクトが大きい場合は、セッション別の使用量を確認します。sys.dm_db_session_space_usage は tempdb 専用のDMVで、セッション単位の割り当てページ数を確認できます。

tempdb-session-usage.sql
SELECT TOP (30)
    ssu.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count) * 8.0 / 1024 AS user_objects_mb,
    (ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) * 8.0 / 1024 AS internal_objects_mb,
    r.status,
    r.command,
    r.wait_type,
    t.text AS running_sql
FROM tempdb.sys.dm_db_session_space_usage AS ssu
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = ssu.session_id
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = ssu.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count
        + ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) DESC;

実行中のリクエストがない sleeping セッションでも、一時テーブルを保持している場合があります。接続プールや手動実行セッションが長く残っている場合は注意してください。

実行中タスクのtempdb使用量を確認する

セッション単位だけでは、現在実行中の並列タスクやリクエスト単位の増加が見えにくいことがあります。実行中のタスク単位では sys.dm_db_task_space_usage を確認します。

tempdb-task-usage.sql
SELECT TOP (30)
    tsu.session_id,
    tsu.request_id,
    (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) * 8.0 / 1024 AS user_objects_mb,
    (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) * 8.0 / 1024 AS internal_objects_mb,
    r.status,
    r.command,
    r.wait_type,
    t.text AS running_sql
FROM tempdb.sys.dm_db_task_space_usage AS tsu
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = tsu.session_id
   AND r.request_id = tsu.request_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count
        + tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC;

バージョンストアが原因の場合

version_store_space_mb が大きい場合は、行バージョン管理が原因です。READ_COMMITTED_SNAPSHOT、SNAPSHOT 分離、オンラインインデックス操作、トリガーなどでバージョンストアが使われます。バージョンストアは、必要としている長時間トランザクションが残っていると解放されにくくなります。

version-store-by-database.sql
SELECT
    DB_NAME(database_id) AS database_name,
    reserved_page_count * 8.0 / 1024 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
ORDER BY version_store_mb DESC;
active-snapshot-transactions.sql
SELECT
    transaction_id,
    session_id,
    elapsed_time_seconds,
    is_snapshot,
    first_snapshot_sequence_num,
    max_version_chain_traversed
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

長時間トランザクションが原因でバージョンストアが解放されない場合、対象セッションの処理内容を確認し、完了、ROLLBACK、またはKILLを検討します。トランザクションログ肥大化とも関係するため、SQL Serverのトランザクションログがいっぱいになる原因と対処法 も合わせて確認すると原因を追いやすくなります。

一時テーブルが原因の場合

user_object_space_mb が大きい場合は、明示的に作成された一時テーブルやテーブル変数が原因の可能性があります。大量データを一時テーブルに入れてから加工するバッチ、検索結果を広く保持する処理、削除されないセッションなどで増えやすいです。

原因 確認ポイント 対処
巨大な一時テーブル セッション別使用量、実行SQL 対象列・行数を絞る、インデックスを見直す
一時テーブルの削除漏れ sleepingセッション、接続元 処理終了時にDROP、接続を閉じる
テーブル変数の大量利用 実行計画、推定行数 一時テーブルへの切替や統計情報を考慮する
大量更新の中間結果 バッチ処理、更新件数 分割処理にする

大量更新や一括更新が絡む場合は、SQLのUPDATE文でデータを更新する方法 のように、WHERE句や更新対象の絞り込みも見直します。

ソート・ハッシュ・スプールが原因の場合

internal_object_space_mb が大きい場合は、SQL Server内部の作業領域が増えている可能性があります。大きな ORDER BY、GROUP BY、DISTINCT、Hash Join、Hash Aggregate、スプールなどが代表例です。

処理 tempdbを使いやすい理由 見直しポイント
大きなソート メモリに収まらない並べ替えがtempdbにこぼれる ORDER BY列、インデックス、取得件数
Hash Join ハッシュテーブルが大きいと作業領域が必要 JOIN条件、統計情報、インデックス
Hash Aggregate GROUP BY対象が大きい 集計前の絞り込み、集計キー
Spool 中間結果を再利用するため保存する 実行計画、相関サブクエリ、更新文

このタイプは、実行計画の確認が重要です。ソートやハッシュのワークテーブルが増える場合、インデックス不足や統計情報の古さが背景にあることがあります。

実行計画で見るポイント

tempdb の内部オブジェクトが増える場合、実行計画上ではソート、ハッシュ、スプール、メモリ不足によるspillを疑います。実行計画で次のような演算子や警告が出ていないか確認します。

見るもの 意味 対策の方向性
Sort 並べ替えに作業領域を使う ORDER BY列に合うインデックス、取得件数削減
Hash Match JOINや集計でハッシュ領域を使う JOIN条件、統計情報、インデックス確認
Table Spool 中間結果を一時保存する 相関サブクエリ、更新文、実行計画の見直し
Spill警告 メモリに収まらずtempdbへこぼれた 統計情報更新、メモリ付与、SQL見直し
Missing Index 不足インデックス候補 そのまま作るのではなく既存インデックスと統合検討

今後、実行計画の記事を追加する場合は、ここから強く内部リンクできます。現時点では、対象SQLの取得件数、JOIN条件、ORDER BY、GROUP BY、統計情報の古さを優先して確認します。

tempdbファイル設定を確認する

原因SQLとは別に、tempdb のファイル設定も確認します。ファイルサイズが小さすぎる、自動拡張が細かすぎる、ディスク容量が少ない、複数ファイルのサイズが不揃い、といった設定はトラブルを悪化させます。

tempdb-file-settings.sql
SELECT
    name,
    type_desc,
    physical_name,
    size * 8 / 1024 AS size_mb,
    CASE max_size
        WHEN -1 THEN 'UNLIMITED'
        WHEN 0 THEN 'NO_GROWTH'
        ELSE CAST(max_size * 8 / 1024 AS varchar(20)) + ' MB'
    END AS max_size,
    CASE is_percent_growth
        WHEN 1 THEN CAST(growth AS varchar(20)) + ' %'
        ELSE CAST(growth * 8 / 1024 AS varchar(20)) + ' MB'
    END AS growth_setting
FROM tempdb.sys.database_files
ORDER BY type_desc, file_id;

自動拡張はパーセント指定より、一定MB指定の方が予測しやすいです。頻繁に自動拡張が起きる環境では、初期サイズを実運用に合わせて大きめに設定します。

tempdbを拡張する例

容量不足が明確な場合は、ディスク空き容量を確認したうえで tempdb ファイルを拡張します。論理ファイル名は環境に合わせて変更してください。

grow-tempdb-file.sql
ALTER DATABASE tempdb
MODIFY FILE (
    NAME = N'tempdev',
    SIZE = 8192MB,
    FILEGROWTH = 512MB
);

複数データファイルを使う場合は、同じサイズ・同じ拡張単位にそろえるのが基本です。ログファイルではなく、データファイル側が不足しているのかも確認してください。

tempdb縮小・再起動の注意点

tempdb はSQL Serverの再起動で再作成されるため、一時的にはサイズが戻ることがあります。しかし、原因SQLや設定が残っていれば再発します。また、本番での再起動は業務停止を伴うため、根本原因調査なしに選ぶ対処ではありません。

対処 効果 注意点
SQL Server再起動 tempdbが再作成される 業務停止。原因が残ると再発
DBCC SHRINKFILE 一時的にファイルを縮小できる 稼働中はブロックや再拡張に注意
原因SQLの停止 使用量増加を止められる 業務影響とロールバック時間を確認する
ファイル拡張 緊急の空き容量を確保できる ディスク空き容量と恒久対策が必要

セッションを終了する場合は、ロールバックやロック影響も確認します。ロック調査全般は SQLのロック調査・解除完全ガイド も参考になります。

tempdb使用量の監視SQL

再発防止には、tempdb の使用内訳を定期的に記録しておくと便利です。次のSQLは、全体容量、空き容量、ユーザーオブジェクト、内部オブジェクト、バージョンストアをまとめて確認する例です。

monitor-tempdb-usage.sql
SELECT
    SYSDATETIME() AS collected_at,
    SUM(total_page_count) * 8.0 / 1024 AS total_mb,
    SUM(unallocated_extent_page_count) * 8.0 / 1024 AS free_mb,
    SUM(user_object_reserved_page_count) * 8.0 / 1024 AS user_object_mb,
    SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS internal_object_mb,
    SUM(version_store_reserved_page_count) * 8.0 / 1024 AS version_store_mb,
    (SUM(total_page_count) - SUM(unallocated_extent_page_count)) * 100.0
        / NULLIF(SUM(total_page_count), 0) AS used_percent
FROM tempdb.sys.dm_db_file_space_usage;

使用率だけでなく、どの内訳が増えているかを保存すると、一時テーブル型の問題なのか、内部オブジェクト型の問題なのか、バージョンストア型の問題なのかを後から追いやすくなります。

monitor-top-tempdb-sessions.sql
SELECT TOP (10)
    SYSDATETIME() AS collected_at,
    ssu.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count) * 8.0 / 1024 AS user_objects_mb,
    (ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) * 8.0 / 1024 AS internal_objects_mb
FROM tempdb.sys.dm_db_session_space_usage AS ssu
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = ssu.session_id
ORDER BY (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count
        + ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) DESC;

やってはいけない対処

tempdb がいっぱいになると緊急対応になりがちですが、次の対処は避けます。

NG対処 なぜ危険か 代わりにやること
原因を見ずに再起動だけする 再発し、原因ログも追いにくくなる DMVで内訳とセッションを確認する
OS上でtempdbファイルを削除する SQL Serverが管理するファイルを壊す危険がある SQL Serverの操作で対応する
自動拡張を極端に小さくする 頻繁な拡張で性能が落ちる 適切な初期サイズと拡張単位を設定する
一時テーブルを無制限に使う tempdbを使い切る 件数を絞る、分割する、不要列を持たない
長時間トランザクションを放置する バージョンストアが解放されにくい 原因セッションを特定する

再発防止策

tempdb の再発防止では、容量監視だけでなく、使用内訳の監視とSQLの見直しが重要です。

対策 内容
使用内訳を監視する ユーザーオブジェクト、内部オブジェクト、バージョンストアを分けて見る
セッション別使用量を記録する 増加時にどの処理が原因か追えるようにする
大きなソート・集計を見直す 実行計画、インデックス、統計情報を確認する
一時テーブルの設計を見直す 不要列を減らし、必要ならインデックスを貼る
ファイルサイズを適正化する 初期サイズと自動拡張単位を運用に合わせる
長時間トランザクションを減らす バージョンストア肥大化を防ぐ

よくある質問

tempdbがいっぱいになったら再起動すればよいですか？

再起動で一時的に解消することはありますが、根本原因の解決ではありません。本番では業務停止も伴うため、まず使用内訳と原因セッションを確認します。

tempdbを使っているSQLはどう確認しますか？

sys.dm_db_session_space_usage と sys.dm_exec_requests、sys.dm_exec_sql_text を組み合わせて確認します。実行中タスクは sys.dm_db_task_space_usage も使います。

version storeが増える原因は何ですか？

スナップショット分離、READ_COMMITTED_SNAPSHOT、オンライン操作などで行バージョンが生成されます。長時間トランザクションがあると解放されにくくなります。

一時テーブルを使うと必ずtempdbが肥大化しますか？

必ずではありません。少量なら問題になりにくいです。ただし、大量データ、不要列、削除漏れ、長時間セッションがあると肥大化しやすくなります。

tempdbファイルはいくつ用意すべきですか？

環境や負荷によります。まずは競合や待機、ファイル使用量の偏りを見ます。複数ファイルにする場合は、同じサイズ・同じ拡張単位にそろえるのが基本です。

tempdbの内部オブジェクトが多い場合は何を見ればよいですか？

実行計画で Sort、Hash Match、Spool、spill警告を確認します。インデックス不足、統計情報の古さ、取得件数の多さが原因になりやすいです。

tempdbの監視は使用率だけで十分ですか？

使用率だけでは不十分です。ユーザーオブジェクト、内部オブジェクト、バージョンストアのどれが増えているかも記録すると、原因調査がしやすくなります。

まとめ

SQL Serverの tempdb が肥大化したときは、まず sys.dm_db_file_space_usage で、ユーザーオブジェクト、内部オブジェクト、バージョンストアのどれが増えているかを確認します。そのうえで、セッション別・タスク別の使用量を追い、原因SQLや長時間トランザクションを特定します。

再起動や縮小だけでは根本解決になりません。一時テーブルの使い方、ソートやハッシュが発生するSQL、バージョンストアを保持する長時間トランザクション、tempdbファイル設定を見直し、再発しにくい運用に整えましょう。