TempDB 分配競爭(PAGELATCH_UP on PFS/GAM/SGAM)是高並發系統的常見痛點。雖然增加資料檔案能緩解,但系統目錄頁(sysallocunits 等)的爭用仍難根治。本文揭示游標使用 Worktable 機制,繞過系統目錄頁分配的特性,在極端場景下可替代臨時表/表變數,降低深層競爭。
建立臨時表 #t 觸發流程:
高並發下,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 系統物件。
游標、DBCC CHECKDB、Merge Join、Exchange Spill 等內部操作使用 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(逐行處理)效能差。
FAST_FORWARD 游標處理流式邏輯。sys.dm_os_waiting_tasks 中 PAGELATCH_UP on PFS/GAM/SGAM,結合 sys.dm_db_session_space_usage 定位高 TempDB 使用者,引導改寫而非單純加檔案。複製 SQL 字串至程式碼時,若前方含全形空格(U+3000),SSMS 不視為關鍵字分隔符,報錯 Incorrect syntax near ' '。解法:刪除重鍵或用 REPLACE(@sql, NCHAR(12288), ' ') 清理。
理解 TempDB 分配架構、Worktable 機制、游標輕量特性,能在極端並發場景下多一張「王牌」。但原則依然是:優先用集合操作,次選臨時表/表變數,最後才考慮游標。任何優化都應建立在測量與業務容忍度之上。