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

このトピックでは、tempdb データベースのディスク領域の不足により発生する問題の診断とトラブルシューティングに役立つ手順と推奨事項について説明します。tempdb のディスク領域が不足すると、SQL Server の実稼働環境で重大な障害が発生したり、実行中のアプリケーションの操作を完了できなくなる場合があります。

tempdb の領域に関する要件

tempdb システム データベースは、SQL Server のインスタンスに接続しているすべてのユーザーが使用できるグローバル リソースです。tempdb データベースはユーザー オブジェクト、内部オブジェクト、およびバージョン ストアの格納に使用されます。SQL Server 2005 では、以前のバージョンの SQL Server より多くのディスク領域が tempdb で必要となります。詳細については、「tempdb に使用するディスク領域の計画」を参照してください。

sys.dm_db_file_space_usage 動的管理ビューを使用して、tempdb ファイルのユーザー オブジェクト、内部オブジェクト、およびバージョン ストアで使用されるディスク領域を監視することができます。また、tempdb のページの割り当てまたは割り当て解除の状態をセッション レベルまたはタスク レベルで監視するには、sys.dm_db_session_space_usage 動的管理ビューと sys.dm_db_task_space_usage 動的管理ビューを使用できます。これらのビューを使用すると、tempdb の大量のディスク領域を使用している大きなクエリ、一時テーブル、またはテーブル変数を特定できます。

tempdb のディスク領域に関する問題の診断

次の表に、tempdb データベースのディスク領域が不足していることを通知するエラーを示します。これらのエラー メッセージは、SQL Server エラー ログで確認できます。また、実行中のアプリケーションにも表示される場合があります。

tempdb データベースの自動拡張が設定されている場合や、データベースのサイズが急増している場合にも、このデータベースのディスク領域に関する問題が発生します。

tempdb のディスク領域の監視

次の例では、tempdb の使用可能な空き領域と、バージョン ストア、内部オブジェクト、およびユーザー オブジェクトにより使用される領域を確認する方法について説明します。

tempdb の使用可能な空き領域の確認

次のクエリを実行すると、tempdb のすべてのファイル内の空きページの合計数と空き領域の合計 (MB 単位) が返されます。

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

バージョン ストアにより使用される領域の確認

次のクエリを実行すると、バージョン ストアにより使用される tempdb のページ数の合計と領域の合計 (MB 単位) が返されます。

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 sys.dm_db_file_space_usage;

実行時間の最も長いトランザクションの確認

バージョン ストアにより tempdb の領域が大量に使用されている場合、実行時間の最も長いトランザクションを特定する必要があります。アクティブなトランザクションを実行時間の長い順に一覧表示するには、次のクエリを使用します。

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

オンラインのインデックス操作に関連付けられていない実行時間の長いトランザクションには、大きなバージョン ストアが必要です。このバージョン ストアでは、トランザクションの開始以降に生成されたすべてのバージョンが保持されます。オンラインのインデックス構築トランザクションは完了するのに時間がかかることがありますが、使用されるバージョン ストアは、オンラインのインデックス操作専用の別のバージョン ストアです。したがって、これらの操作により、他のトランザクションのバージョンを削除できなくなることはありません。詳細については、「行のバージョン管理用リソースの使用状況」を参照してください。

内部オブジェクトにより使用される領域の確認

次のクエリを実行すると、tempdb の内部オブジェクトにより使用されるページ数の合計と領域の合計 (MB 単位) が返されます。

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 sys.dm_db_file_space_usage;

ユーザー オブジェクトにより使用される領域の確認

次のクエリを実行すると、tempdb のユーザー オブジェクトにより使用されるページ数の合計と領域の合計 (MB 単位) が返されます。

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 sys.dm_db_file_space_usage;

領域の合計 (空き領域と使用中の領域) の確認

次のクエリを実行すると、tempdb のすべてのファイルにより使用されているディスク領域の合計 (MB 単位) が返されます。

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

クエリにより使用される領域の監視

tempdb の領域の使用に関する最も一般的な問題として、大量の領域を使用する大きなクエリに関する問題があります。通常、ほとんどの領域は作業テーブルや作業ファイルなどの内部オブジェクトにより使用されます。したがって、内部オブジェクトにより使用される領域を監視すると、どれぐらいの領域が使用されているかを確認できますが、その領域を使用しているクエリを直接特定することはできません。

次の方法は、tempdb の大部分の領域を使用しているクエリの特定に役立ちます。1 つ目の方法ではバッチレベルのデータを調べるので、2 つ目の方法よりもデータを集中的に使用しません。2 つ目の方法は、大量のディスク領域を使用している特定のクエリ、一時テーブル、またはテーブル変数の特定に使用できますが、結果を得るために多くのデータを収集する必要があります。

方法 1 : バッチレベルの情報

バッチ要求に含まれているクエリが少数で、そのうちの 1 つのみが複雑なクエリである場合、通常はこの情報だけで、どのバッチが領域を大量に使用しているかを十分に把握できます。特定のクエリに関する情報は不要です。

この方法を続行するには、数分のポーリング間隔で sys.dm_db_session_space_usage 動的管理ビューと sys.dm_db_task_space_usage 動的管理ビューからポーリングするように SQL Server エージェント ジョブをセットアップする必要があります。次の例では、ポーリング間隔を 3 分に設定します。sys.dm_db_session_space_usage には現在アクティブなタスクの割り当ての状態が含まれないので、両方のビューからポーリングする必要があります。2 つの期間で割り当てられたページ数を比較すると、その期間と期間の間に割り当てられたページ数を計算できます。

次の例では、SQL Server エージェント ジョブに必要なクエリを提供します。

A. 各セッションで現在実行中のすべてのタスクで内部オブジェクトにより使用されている領域の確認

次の例では、all_task_usage というビューを作成します。クエリを実行すると、tempdb の現在実行中のすべてのタスクで内部オブジェクトにより使用されている領域の合計がビューから返されます。

CREATE VIEW all_task_usage
AS 
    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;
GO

B. 現在のセッションの中で実行中のタスクと完了済みのタスクの両方で内部オブジェクトにより使用されている領域の確認

次の例では、all_session_usage というビューを作成します。クエリを実行すると、tempdb の実行中のタスクと完了済みのタスクですべての内部オブジェクトにより使用されている領域がビューから返されます。

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

これらのビューに 3 分ごとにクエリを実行して、結果セットとして次の情報が提供されたとします。

• セッション 71 が開始してから午後 5 時までに 100 ページが割り当てられ、100 ページが割り当て解除されました。
• セッション 71 が開始してから午後 5 時 3 分までに 20,100 ページが割り当てられ、100 ページが割り当て解除されました。

この情報を分析すると、2 つの測定期間の間に、セッションで内部オブジェクトに 20,000 ページが割り当てられたが、割り当て解除されたページはないことがわかります。これにより、問題が発生する可能性があります。

メモ :
データベース管理者は、3 分よりも短い間隔でのポーリングを指定できます。ただし、クエリの実行時間が 3 分よりも短い場合、そのクエリにより tempdb の領域が大量に使用される可能性は低くなります。

その時点で実行されているバッチを確認するには、SQL Server Profiler を使用して RPC:Completed イベント クラスと SQL:BatchCompleted イベント クラスをキャプチャします。

SQL Server Profiler の使用に代わる方法は、次の例に示すように、すべてのセッションで 3 分ごとに DBCC INPUTBUFFER を実行することです。

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

方法 2 : クエリレベルの情報

入力バッファや SQL Server Profiler イベントの SQL:BatchCompleted を確認しただけでは、tempdb のディスク領域の大部分を使用しているクエリを特定できない場合があります。次の方法を使用するとこの問題を解決できますが、方法 1 で定義した手順よりも多くのデータを収集する必要があります。

この方法を続行するには、sys.dm_db_task_space_usage 動的管理ビューからポーリングする SQL Server エージェント ジョブのジョブをセットアップします。ポーリング間隔は、方法 1 と比較すると短く、1 分間に 1 回とします。間隔を短くするのは、クエリ (タスク) が実行されていない場合、sys.dm_db_task_space_usage はデータを返さないためです。

ポーリング クエリでは、sys.dm_db_task_space_usage 動的管理ビューに定義されたビューが sys.dm_exec_requests と結合され、sql_handle、statement_start_offset、statement_end_offset、および plan_handle の各列を返します。

CREATE VIEW all_request_usage
AS 
  SELECT session_id, request_id, 
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
  SELECT R1.session_id, R1.request_id, 
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO

クエリ プランがキャッシュに存在する場合、クエリとクエリ実行プランの Transact-SQL テキストを XML プラン表示形式でいつでも取得できます。実行されるクエリの Transact-SQL テキストを取得するには、sql_handle 値と sys.dm_exec_sql_text 動的管理関数を使用します。クエリ実行プランを取得するには、plan_handle 値と sys.dm_exec_query_plan 動的管理関数を使用します。

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

クエリ プランがキャッシュに存在しない場合は、次のいずれかの方法を使用して、クエリとクエリ実行プランの Transact-SQL テキストを取得できます。

A. ポーリングの使用

all_query_usage ビューからポーリングし、次のクエリを実行してクエリ テキストを取得します。

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

sql_handle はそれぞれの一意なバッチに対して一意であるため、sql_handle の重複したエントリを保存する必要はありません。

プラン ハンドルと XML プランを保存するには、次のクエリを実行します。

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

B. SQL Server Profiler イベントの使用

sys.dm_exec_sql_text 関数と sys.dm_exec_query_plan 関数のポーリングに代わる方法として、SQL Server Profiler イベントを使用できます。Profiler イベントには、生成されるクエリ プランとクエリ テキストのキャプチャに使用できるものがあります。たとえば、イベント 165 は、トレース、SQL テキスト、クエリ プラン、およびクエリ統計情報のパフォーマンス統計を返します。

一時テーブルとテーブル変数により使用される領域の監視

一時テーブルと一時変数により使用される領域を監視するために、ポーリング クエリに似た方法を使用できます。一時テーブルまたは一時変数内の大量のユーザー データを取得するアプリケーションにより、tempdb の領域の使用に関する問題が発生する可能性があります。これらのテーブルまたは変数はユーザー オブジェクトに属しています。sys.dm_db_session_space_usage 動的管理ビューの user_objects_alloc_page_count 列と user_objects_dealloc_page_count 列を使用し、上記で説明した方法に従ってください。

セッションごとのページの割り当ておよび割り当て解除の監視

次の表は、指定したセッションの sys.dm_db_file_space_usage、sys.dm_db_session_space_usage、および sys.dm_db_task_space_usage 動的管理ビューにより返される結果を示しています。各行は、指定したセッションにおける tempdb の割り当てまたは割り当て解除の状態を表しています。この状態は、"イベント" 列に記載されています。残りの列は、動的管理ビューの列に返される値を示しています。

このシナリオは、tempdb データベースの未割り当てのエクステントに 872 ページ、ユーザー オブジェクトにより予約されているエクステントに 100 ページが存在する状態から開始します。また、1 つのユーザー テーブルに 10 ページを割り当ててから、それらのページの割り当てをすべて解除します。最初の 8 ページには混合エクステントを割り当てます。残りの 2 ページには単一エクステントを割り当てます。

参照

概念

tempdb のパフォーマンスの最適化
tempdb データベース
tempdb に使用するディスク領域の計画

その他の技術情報

データベース エンジンのトラブルシューティング

ヘルプおよび情報

SQL Server 2005 の参考資料の入手