共用方式為


tempdb 磁碟空間不足的疑難排解

此主題提供了程序和建議,可幫助您診斷和疑難排解因 tempdb 資料庫磁碟空間不足而造成的問題。tempdb 的磁碟空間用完會造成 SQL Server 實際執行環境嚴重中斷,並會讓執行中的應用程式無法完成作業。

tempdb 空間需求

tempdb 系統資料庫是全域資源,可供所有連接到 SQL Server 執行個體的使用者使用。tempdb 資料庫是用來儲存下列物件:使用者物件、內部物件和版本存放區。

您可以使用 sys.dm_db_file_space_usage 動態管理檢視,來監視 tempdb 檔案中的使用者物件、內部物件和版本存放區所使用的磁碟空間。另外,若要在工作階段或工作層級監視 tempdb 中的頁面配置或取消配置活動,您可以使用 sys.dm_db_session_space_usagesys.dm_db_task_space_usage 動態管理檢視。這些檢視可用來識別使用大量 tempdb 磁碟空間的大型查詢、暫存資料表或資料表變數。

診斷 tempdb 磁碟空間問題

下表列出的錯誤訊息,指出 tempdb 資料庫的磁碟空間不足。這些錯誤可以在 SQL Server 錯誤記錄檔中找到,也可能傳回至任何執行中的應用程式。

錯誤

引發時間

1101 或 1105

任何工作階段都必須在 tempdb 中配置空間。

3959

版本存放區已滿。此錯誤通常出現在記錄檔中的 1105 或 1101 錯誤之後。

3967

版本存放區被強制壓縮,因為 tempdb 已滿。

3958 或 3966

交易無法在 tempdb 中找到必要的版本記錄。

當資料庫設為自動成長,且資料庫大小快速增加時,也會指出有 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 中使用者物件使用的總頁數和使用者物件使用的總空間。

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

監視查詢使用的空間

tempdb 空間使用問題中最常見的類型之一,是與使用大量空間的大型查詢有關。通常,此空間是用於內部物件,如工作資料表或工作檔案。雖然監視內部物件使用的空間可讓您知道使用了多少空間,但它不會直接識別正在使用該空間的查詢。

下列方法可幫助識別正在使用 tempdb 中大部份空間的查詢。第一個方法會檢查批次層級的資料,使用的資料量比第二個方法少。第二個方法則可用來識別耗用磁碟空間的特定查詢、暫存資料表或資料表變數,但必須收集更多資料才能得到答案。

方法 1:批次層級資訊

如果批次要求只包含少數查詢,而且其中只有一個是複雜的查詢,通常就有足夠資訊知道是哪一個批次 (而非特定查詢) 在耗用空間。

若要繼續使用此方法,必須將 SQL Server Agent 作業設定為使用輪詢間隔 (範圍約在幾分鐘之間),從 sys.dm_db_session_space_usagesys.dm_db_task_space_usage 動態管理檢視中輪詢。下列範例使用 3 分鐘的輪詢間隔。您必須從兩個檢視中輪詢,因為 sys.dm_db_session_space_usage 不包括目前使用中工作的配置活動。比較兩個時間間隔中配置的頁數差異,可讓您計算兩個間隔之間配置了多少頁數。

下列範例提供 SQL Server Agent 作業所需的查詢。

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

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

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

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

[!附註]

身為資料庫管理員的您,可能會決定以小於 3 分鐘的頻率更頻繁地輪詢。不過,如果查詢執行的時間少於 3 分鐘,查詢可能不會耗用 tempdb 的大量空間。

若要判斷在該時間內執行的批次,請使用 SQL Server Profiler 來擷取 RPC:CompletedSQL: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 所定義的程序更多的資料。

若要繼續使用此方法,請設定 SQL Server Agent 作業 的作業,從 sys.dm_db_task_space_usage 動態管理檢視中輪詢。相較於方法 1,輪詢間隔應該短一點,一分鐘一次。使用短間隔的原因,是假如目前沒有執行查詢 (工作) 時,sys.dm_db_task_space_usage 不會傳回資料。

在輪詢查詢中,定義在 sys.dm_db_task_space_usage 動態管理檢視中的檢視會和 sys.dm_exec_requests 聯結,來傳回 sql_handle, statement_start_offsetstatement_end_offsetplan_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_textsys.dm_exec_query_plan 函數,另一個替代方法是使用 SQL Server Profiler 事件。有一些 Profiler 事件可用來擷取查詢計畫和產生的查詢文字。例如「事件 165」會傳回追蹤的效能統計資料、SQL 文字、查詢計畫和查詢統計資料。

監視暫存資料表和資料表變數所使用的空間

您可以使用類似輪詢查詢的方法,來監視暫存資料表和暫存變數所使用的空間。會取得暫存資料表或暫存變數內大量使用者資料的應用程式,會造成 tempdb 的空間使用問題。這些資料表或變數屬於使用者物件。您可以在 sys.dm_db_session_space_usage 動態管理檢視中使用 user_objects_alloc_page_countuser_objects_dealloc_page_count 資料行,並遵循使用前述的方法。

按工作階段監視頁面配置和取消配置

下表顯示 sys.dm_db_file_space_usagesys.dm_db_session_space_usagesys.dm_db_task_space_usage 動態管理檢視針對指定的工作階段傳回的結果。每一資料列代表指定的工作階段在 tempdb 中的配置或取消配置活動。活動都列在事件資料行中。其餘資料行顯示動態管理檢視的資料行中會傳回的值。

以此案例而言,假設 tempdb 資料庫開始時有 872 頁在未配置範圍,有 100 頁在使用者物件保留範圍。工作階段配置 10 頁給使用者資料表,然後再全部取消配置。前 8 頁是在混合範圍內。剩餘 2 頁是在制式範圍內。

事件

dm_db_file_space_usage

unallocated_extent_page_count 資料行

dm_db_file_space_usage

user_object_reserved_page_count 資料行

dm_db_session_space_usage

and dm_db_task_space_usage

user_object_alloc_page_count 資料行

dm_db_session_space_usage

and dm_db_task_space_usage

user_object_dealloc_page_count 資料行

啟動

872

100

0

0

從現有的混合範圍配置第 1 頁

872

100

1

0

配置第 2 到第 8 頁:耗用一個新的混合範圍

864

80

8

0

配置第 9 頁:耗用一個新的制式範圍

856

108

16

0

從現有的制式範圍配置第 10 頁

856

108

16

0

從現有的制式範圍取消配置第 10 頁

856

108

16

0

取消配置第 9 頁和制式範圍

864

100

16

8

取消配置第 8 頁

864

100

16

9

取消配置第 7 到第 1 頁,在混合範圍上取消配置

872

100

16

16