萬盛學電腦網

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

SQL Server tempdb 使用規範與效能優化六大原則

tempdb 特性回顧

  • 系統資料庫,每次啟動重建,不需恢復。
  • 預設 SIMPLE 恢復模式,不可變更。
  • 單一檔案群組,不可新增檔案群組。
  • 所有會話共享,爭用熱點極高。

六大使用規範

  1. 最小化使用
    • 避免不必要的暫存表(#temp、##global)、表變數(@table)。
    • 大量資料暫存表加上索引提升查詢效率。
    • 游標(CURSOR)嚴重拖慢效能,改用集合式操作。
    • CTE、SORT_IN_TEMPDB、Online Index Rebuild、Trigger 工作表、排序、Hash Match、Exchange Spill 皆使用 tempdb,評估必要性。
  2. 預先配置足夠初始大小
    • 預設 8MB + 10% 自動成長,大型負載會頻繁自動成長導致效能抖動。
    • 啟動時設定 MDF/LDF 初始大小(如 MDF 300GB、LDF 50GB),或用 ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, SIZE=300GB)。
    • 觀察穩態期大小作為基準,預留 20% 緩衝。
  3. 避免 SHRINK
    • SHRINK 會造成碎片、鎖定系統表、觸發自動成長惡性循環。
    • 僅在異常暴增(如一次性大報表)事後人工縮減,並立即重建索引。
  4. 檔案分離至獨立高速儲存
    • tempdb 資料檔(.mdf/.ndf)與記錄檔(.ldf)放在獨立 NVMe/SSD,與使用者資料庫分離。
    • 資料檔數量 = 邏輯 CPU 核心數(≤8 核)或 8 個起始,以降低 SGAM/PFS/GAM 爭用。
    • 所有檔案相同大小、相同成長增量,確保比例填滿演算法均勻分佈。
  5. 大量臨時表與大表 JOIN 謹慎
    • 會在單一陳述式中多次存取 tempdb 系統表,嚴重拖慢系統表效能。
    • 改用永久暫存表(實體表 + 定時清理)或分批處理。
  6. 明確清理與權限控制
    • 程序結束時 TRUNCATE TABLE #temp; DROP TABLE #temp; 釋放系統表鎖定。
    • 限制非 DSA 建立暫存表權限(預設 PUBLIC 有權限,不可撤銷,但可透過資料庫角色控制永久物件建立)。

監控腳本:tempdb 空間與爭用

-- 空間使用
SELECT 
  SUM(unallocated_extent_page_count)*1.0/128 AS [Free Space (MB)],
  SUM(version_store_reserved_page_count)*1.0/128 AS [Version Store (MB)],
  SUM(internal_object_reserved_page_count)*1.0/128 AS [Internal Objects (MB)],
  SUM(user_object_reserved_page_count)*1.0/128 AS [User Objects (MB)],
  SUM(mixed_extent_page_count)*1.0/128 AS [Mixed Extents (MB)]
FROM sys.dm_db_file_space_usage WHERE database_id = 2;

-- 爭用熱點 (PFS/GAM/SGAM)
SELECT * FROM sys.dm_os_waiting_tasks
WHERE resource_description LIKE '2:%' AND wait_type LIKE 'PAGE%LATCH%';

常見誤區

  • ❌ 以為表變數 @t 不用 tempdb → 錯,表變數也在 tempdb 建立,只是統計資訊較少。
  • ❌ 認為 #temp 只對當前連線可見 → 正確,但巢狀程序可見父層 #temp。
  • ❌ 直接在 tempdb 建立永久表 → 重啟消失,請建於使用者資料庫。

遵循規範並持續監控,tempdb 將不再是效能瓶頸。

copyright © 萬盛學電腦網 all rights reserved