萬盛學電腦網

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

SQL Server 查詢暫存檔 (tmp) 機制深度解析與磁碟空間規劃指南

前言

執行查詢時,SQL Server 會在 `tempdb` 及作業系統暫存目錄 (`%TEMP%`/`%TMP%`) 建立暫存檔 (副檔名 `.tmp`) 儲存中間結果集。理解其儲存格式與空間需求,對大量資料查詢效能調校至關重要。

一、暫存檔位置與環境變數

Windows 系統環境變數 `TEMP` / `TMP` 決定暫存檔實體路徑:

控制台 → 系統 → 進階 → 環境變數 → 使用者/系統變數 TEMP, TMP
典型路徑:C:\Windows\Temp、C:\Users\{User}\AppData\Local\Temp

二、暫存檔內部儲存格式 (二進位結構)

每欄位分兩段儲存:

  1. 長度前綴 (Length Prefix):
    • 資料長度 ≤ 255 Bytes:1 Byte 儲存長度。
    • 資料長度 > 255 Bytes:5 Bytes (1 Byte 標誌 + 4 Bytes 整數長度)。
  2. 實際資料:
    • 字串型 (`char`/`varchar`/`nchar`/`nvarchar`):轉為 **Unicode (UTF-16LE)** 儲存,每字元 2 Bytes。
    • 數值/日期型:依資料類型固定長度二進位儲存 (如 `bigint` 8 Bytes, `datetime` 8 Bytes)。

實際範例:`SELECT 1, CAST(1 AS BIGINT), 'ab', GETDATE()`

產生的 `.tmp` 十六進位內容:

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

三、大量資料查詢暫存檔大小估算模型

假設資料表結構:

CREATE TABLE TestData (
    ID INT PRIMARY KEY,
    DTCol DATETIME,
    CharCol CHAR(100),
    MaxCol VARCHAR(8000)
);

單列暫存檔佔用估算:

欄位長度前綴資料長度小計
ID (INT)1B4B5B
DTCol (DATETIME)1B8B9B
CharCol (CHAR(100))1B200B (100*2 Unicode)201B
MaxCol (VARCHAR(8000))5B (>255)16000B (8000*2)16005B
單列總計~16,220 Bytes (~15.8 KB)

100 萬列預估暫存檔大小:16,220 * 1,000,000 ≈ 15.5 GB。

四、暫存檔對查詢效能與穩定性的影響

風險 1:磁碟空間耗盡導致查詢中止

  • 暫存目錄所在磁碟剩餘空間 < 暫存檔需求 → 錯誤訊息「磁碟空間不足」→ 查詢終止。
  • 常見於大型報表、未加索引的大表掃描、大量排序/雜湊運算溢寫 `tempdb`。

風險 2:FAT32 檔案系統 4GB 單檔限制

  • 若暫存目錄在 FAT32 磁碟區,單一 `.tmp` 檔超過 4GB 即寫入失敗,即使磁碟總空間充足。
  • SQL Server **不會** 自動拆分多個暫存檔繞過此限制。

五、最佳實務建議

  1. 暫存目錄放置於 NTFS 格式、大空間磁碟區 (建議獨立 SSD/高速 HDD)。
  2. 調整 `tempdb` 位置與大小: 將 `tempdb` 資料/記錄檔移至專用高速磁碟,預先設定足夠初始大小避免自動增長延遲。
  3. 優化查詢減少溢寫: 加強索引覆蓋、避免 `SELECT *`、分頁查詢、適當使用 `OPTION (MAXDOP 1)` 降低平行度減少暫存記憶體壓力。
  4. 監控指標: `Performance Monitor` → `SQLServer:Databases` → `Tempdb` 相關計數器 / `Physical Disk` → `Free Space`。

檢視暫存檔內容工具

  • 記事本:僅能看字串部分,數值/日期顯示亂碼。
  • UltraEdit (UE) / WinHex / 010 Editor:十六進位模式完整解析二進位結構。
copyright © 萬盛學電腦網 all rights reserved