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 中使用者物件使用的總頁數和使用者物件使用的總空間。

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 中所有檔案使用的總磁碟空間量。

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 分鐘的間隔來接受查詢，結果集將提供下列資訊。

• 在下午 5:00，自工作階段啟動之後，工作階段 71 已配置 100 頁及取消配置 100 頁。

• 在下午 5:03，自工作階段啟動之後，工作階段 71 已配置 20100 頁及取消配置 100 頁。

當您分析此資訊時，可得知在兩個測量之間：工作階段已配置 20,000 頁給內部物件，且未取消配置任何一頁。這指出有潛在的問題。

若要判斷在該時間內執行的批次，請使用 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 文字、查詢計畫和查詢統計資料。