SQL 查詢產生的暫存檔對資料庫效能的影響：TMP 檔案格式、磁碟空間與 NTFS 必須性深度分析

執行大量資料查詢時，SQL Server 會在 TEMP 目錄建立 .tmp 暫存檔儲存中間結果。本文深入剖析 TMP 檔案儲存格式、大小估算、對查詢效能的影響，以及為何必須將 TEMP 目錄置於 NTFS 分區且預留充足空間。

TMP 檔案儲存位置

• Windows 環境變數 TEMP / TMP 指定的資料夾（通常 C:\Windows\Temp 或使用者設定檔下）。
• 可透過「我的電腦 → 內容 → 進階 → 環境變數」檢視/修改。

TMP 檔案內部格式（每欄位兩段式）

1. 長度標示：≤255 字元 → 1 byte；>255 → 5 bytes。
2. 資料本體：字元型資料以 **Unicode (UTF-16)** 儲存；數值/日期以二進位原生格式儲存。

實際範例：查詢 SELECT 1, CAST(1 AS BIGINT), 'ab', GETDATE()

產生的 TMP 檔案十六進位內容：

04 01 00 00 00     -- INT 1 (長度4 + 值1)
08 01 00 00 00 00 00 00 00  -- BIGINT 1 (長度8 + 值1)
04 61 00 62 00     -- 'ab' (長度4 + Unicode 'a''b')
08 60 73 C2 02 7A 7B CB 08  -- GETDATE() (長度8 + 二進位日期時間)

資料表層級儲存順序

對於 SELECT * FROM table，TMP 檔依欄位順序依序寫入：

[id長度][id資料][dtcol長度][dtcol資料][charcol長度][charcol資料][maxcol長度][maxcol資料]...

字元型欄位 (CHAR/VARCHAR/TEXT/MAX) 皆轉 Unicode 儲存，佔用雙倍空間。

大量資料查詢的空間估算實例

假設表結構：id(INT)、dtcol(DATETIME)、charcol(CHAR 100)、maxcol(VARCHAR(MAX) 7200 bytes)

• 單列 TMP 大小 ≈ 1+4 + 1+8 + 1+200 + 5+7200 = **7,424 bytes**
• 100 萬列 ≈ **7.25 GB** 暫存空間

兩大效能風險與對策

最佳實務建議

• TEMP 目錄專用磁碟/分區：獨立高速 SSD/RAID，格式化為 NTFS，預留總資料量 2~3 倍空間。
• 調整 TempDB 設定：多資料檔（每核心 1 檔，上限 8）、啟用瞬間檔案初始化、預先配置足夠大小避免自動成長。
• 查詢優化：避免 SELECT *、大量文字欄位查詢、無條件全表掃描；使用分頁、篩選、投影減少中間結果集。
• 監控指標：Performance Monitor → SQL Server:TempDB → Temp Tables Creation Rate / Temp Tables For Destruction Rate。

查看 TMP 檔案工具

• 記事本：僅能看字元資料，數值/日期顯示亂碼。
• UltraEdit (UE) / WinHex / 010 Editor：二進位模式完整解析結構。

理解 TMP 暫存檔機制，並從儲存層面規劃 TEMP 環境，是防止大查詢失敗、穩定 SQL Server 效能的關鍵基建工作。