tempdb 是 SQL Server 的共享系統數據庫,存儲:
#temp、##global_temp)、表變量(@table_var)所有會話共享同一 tempdb,爭用直接影響整個實例性能。
盡量避免或優化:DISTINCT、ORDER BY、GROUP BY、HAVING、大結果集 JOIN、COMPUTE。這些操作常溢出到 tempdb 排序/雜湊。
對策:適當索引覆蓋查詢、分頁優化、預聚合、調整 max degree of parallelism (MAXDOP)。
CREATE #temp / DROP #temp,造成系統表(sysobjects、syscolumns、sysindexes)鎖爭用。@table):僅駐留內存(小數據量)、無統計信息、不觸發重編譯、事務回滾不影響。#temp 並顯式創建索引。-- 避免:CREATE #t ...; INSERT #t SELECT ... (產生大量日誌、鎖系統表)
-- 推薦:SELECT INTO(最小日誌、無系統表鎖爭用)
SELECT * INTO #big_temp FROM large_table WHERE ...;
-- 若需索引,分兩步:
SELECT * INTO #big_temp FROM large_table WHERE ...;
CREATE INDEX idx_col ON #big_temp(col);
將 CREATE #temp + CREATE INDEX 放入獨立子存儲過程中,確保優化器能識別並使用索引(避免參數嗅探導致計劃不穩)。
-- 存儲過程結束前
TRUNCATE TABLE #temp; -- 極快,僅釋放頁,不記錄行刪除日誌
DROP TABLE #temp; -- 釋放元數據鎖
-- 全局臨時表 ##temp 同理,但作用域為實例級,更需及時清理
避免依賴會話結束自動清理,長連接(連接池)會導致 tempdb 膨脹。
大臨時表與大業務表 JOIN/UPDATE 會多次訪問系統表,放大爭用。評估是否可改寫為永久表、分批處理或調整查詢邏輯。
Trace Flag 1117/1118(SQL 2016+ 默認開啟)實現均勻分配,消除 SGAM/PFS 頁爭用。ALTER DATABASE tempdb ADD FILE (NAME='tempdev2', FILENAME='F:\tempdb\tempdev2.ndf', SIZE=4GB, FILEGROWTH=512MB);
-- 重複添加至 8 文件
SE_MANAGE_VOLUME_NAME(執行卷維護任務),數據文件擴展秒級完成。-- 1. 空間使用
SELECT
SUM(size)*8.0/1024 AS total_size_mb,
SUM(FILEPROPERTY(name,'SpaceUsed'))*8.0/1024 AS used_mb
FROM tempdb.sys.database_files;
-- 2. 版本存儲大小
SELECT version_store_reserved_page_count * 8.0 / 1024 AS version_store_mb
FROM sys.dm_db_file_space_usage;
-- 3. 爭用熱點(PFS/GAM/SGAM)
SELECT * FROM sys.dm_os_waiting_tasks
WHERE resource_description LIKE '2:%' AND wait_type LIKE 'PAGE%LATCH%';
-- 4. 臨時對象計數
SELECT
SUM(internal_object_reserved_page_count) AS internal_pages,
SUM(user_object_reserved_page_count) AS user_pages
FROM sys.dm_db_task_space_usage;
| 現象 | 可能原因 | 排查動作 |
|---|---|---|
| tempdb 爆滿磁盤 | 長事務、大排序、版本存儲未清理、臨時表未釋放 | 查 sys.dm_tran_active_snapshot_database_transactions、sys.dm_db_session_space_usage 定位會話,KILL 或優化查詢 |
| PAGELATCH_UP/PAGELATCH_EX 等待高 | 數據文件太少、分配頁熱點 | 增加數據文件至 CPU 核心數、啟用 TF1117/1118 |
| 系統表鎖死 | 高頻創建/刪除臨時表 | 改用表變量、緩存臨時表、降低頻率 |
tempdb 是 SQL Server 的「公共速寫板」,優化核心在於:減少使用、均衡 I/O、預分配空間、及時清理。將 tempdb 視為關鍵資源納入容量規劃與日常巡檢,能避免 80% 的突發性能事故。