萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 利用游標緩解 TempDB 系統頁競爭:DBA 與開發必讀

SQL Server 利用游標緩解 TempDB 系統頁競爭:DBA 與開發必讀

TempDB 分配競爭(PAGELATCH_UP on PFS/GAM/SGAM)是高並發系統的常見痛點。雖然增加資料檔案能緩解,但系統目錄頁(sysallocunits 等)的爭用仍難根治。本文揭示游標使用 Worktable 機制,繞過系統目錄頁分配的特性,在極端場景下可替代臨時表/表變數,降低深層競爭。

TempDB 競爭根源回顧

建立臨時表 #t 觸發流程:

  1. 系統表插入條目(系統資料頁)。
  2. 分配 IAM 頁、混合區(更新 PFS)。
  3. 分配資料頁(掃描 SGAM、更新 PFS、更新 IAM)。
  4. 寫入系統表完成。

高並發下,SGAM、PFS、系統表 成為熱點頁。多檔案僅分散配置頁,系統目錄仍為單點。

實測捕捉競爭

-- Session 1:建立臨時表
CREATE TABLE #t (id INT, str1 VARCHAR(10));
-- Session 2:監控等待
SELECT resource_description, * FROM sys.dm_os_waiting_tasks WHERE session_id > 50;

結果顯示 2:1:53(PFS 頁)發生 PAGELATCH_UP。
DBCC PAGE(2,1,53,1) 確認為 sysallocunits 系統物件。

Worktable 與游標的特殊機制

游標、DBCC CHECKDB、Merge Join、Exchange Spill 等內部操作使用 Worktable:

  • Object ID 為負數。
  • 不寫入系統表,無系統目錄頁鎖定。
  • 直接分配資料頁,極輕量。

驗證 Worktable 分配

USE tempdb; CHECKPOINT;
DBCC CHECKDB(master);  -- 產生 Worktable
SELECT Description, * FROM fn_dblog(NULL,NULL);  -- 觀察配置日誌
DBCC PAGE(2,4,104,3);  -- IAM 頁
DBCC PAGE(2,5,104,3);  -- 資料頁,非系統物件

游標實測:零系統頁競爭

-- 游標
DECLARE @cur CURSOR;
SET @cur = CURSOR FOR SELECT * FROM tt;
OPEN @cur; FETCH NEXT FROM @cur; -- 僅分配 Worktable 頁

-- 臨時表(對照)
CREATE TABLE #tt (id INT);
INSERT INTO #tt SELECT * FROM tt;

監控畫面顯示:游標僅出現 Worktable 相關頁,無 PFS/GAM/SGAM 系統頁等待;臨時表則明顯出現系統頁競爭。

適用場景與決策矩陣

場景推薦物件理由
小結果集、需隨機存取、多次掃描表變數 / 臨時表統計資訊、索引支援、計劃重用
大結果集、單向流式讀取、極高並發游標 (FAST_FORWARD/READ_ONLY)零系統目錄競爭、記憶體壓力小
中間結果需 JOIN/聚合臨時表 + 索引優化器可使用統計資訊
一次性資料搬移、無複雜邏輯INSERT...SELECT / BCP集合操作最快
> ⚠️ 警示:游標非銀彈。預設游標預設為動態、可更新,開銷大。必須宣告 FAST_FORWARD, READ_ONLY, LOCAL 才能發揮輕量優勢。濫用游標會導致程式碼難維護、RBAR(逐行處理)效能差。

DBA 與開發協作建議

  • 開發:遇到 TempDB 競爭時,評估是否可改用 FAST_FORWARD 游標處理流式邏輯。
  • DBA:監控 sys.dm_os_waiting_tasks 中 PAGELATCH_UP on PFS/GAM/SGAM,結合 sys.dm_db_session_space_usage 定位高 TempDB 使用者,引導改寫而非單純加檔案。
  • 雙方:建立「TempDB 使用審查清單」,上線前必檢。

附錄:全形空格導致『'' 附近有語法錯誤』

複製 SQL 字串至程式碼時,若前方含全形空格(U+3000),SSMS 不視為關鍵字分隔符,報錯 Incorrect syntax near ' '。解法:刪除重鍵或用 REPLACE(@sql, NCHAR(12288), ' ') 清理。

小結

理解 TempDB 分配架構、Worktable 機制、游標輕量特性,能在極端並發場景下多一張「王牌」。但原則依然是:優先用集合操作,次選臨時表/表變數,最後才考慮游標。任何優化都應建立在測量與業務容忍度之上。

copyright © 萬盛學電腦網 all rights reserved