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

次の例では、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

次の例では、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;

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;

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