SQL Server 踩坑實錄：從 DELETE 不釋放空間到 NOT IN 效能炸彈

這篇文章是我從 DBA 到全端架構師這幾年，在 SQL Server 效能優化上踩過的坑的總整理。不是教科書式的理論，而是每一條都是我實際測試、實際踩雷後的血淚經驗。如果你正在處理 SQL Server 效能優化的問題——DELETE 後空間沒釋放、查詢莫名其妙變慢、鎖定機制搞不清楚——這篇應該能幫你少走不少冤枉路。

---

1. 儲存空間的真相 — DELETE 真的刪除了嗎？

這大概是我當 DBA 第一年最震驚的發現：DELETE 不會釋放磁碟空間。它只是把資料列標記為「ghost record」，等待背景的 Ghost Cleanup 程序來處理。但即使 Ghost Cleanup 跑完了，那些頁面（Page）還是屬於該表的配置空間，不會歸還給作業系統。

如果你想真正回收空間，只有兩條路：

• TRUNCATE TABLE：直接釋放所有資料頁面，包含 7-byte 的列標頭（row header），速度極快，但會清除所有資料。
• ALTER INDEX … REBUILD：重建索引時重新組織頁面，回收碎片化的空間。

三種配置單元（Allocation Unit）

SQL Server 在底層把資料分成三種配置單元儲存：

配置單元	儲存內容	典型欄位類型
IN_ROW_DATA	固定長度 + 行內變動長度資料	char, int, datetime, nvarchar(100)
LOB_DATA	大型物件資料	nvarchar(max), text, image, xml
ROW_OVERFLOW_DATA	超過 8060 bytes 的變動長度資料	nvarchar 超過行內限制時溢出

CHAR vs VARCHAR 的儲存差異

很多人以為「反正都是存字串」，但底層差異巨大。CHAR(100) 不管你存 1 個字還是 100 個字，永遠佔用 100 bytes。VARCHAR(100) 則只儲存實際資料長度加上 2 bytes 的長度前綴。

-- Verify with DATALENGTH()
DECLARE @fixed CHAR(100) = 'Hello';
DECLARE @variable VARCHAR(100) = 'Hello';

SELECT DATALENGTH(@fixed) AS CharLength,      -- Result: 100
       DATALENGTH(@variable) AS VarcharLength; -- Result: 5

另外要注意：索引佔用的是真實空間（IN_ROW_DATA）。當你清空表後，索引也被清空。但只要 INSERT 新資料，索引會立即重新填充。而且 VARCHAR 欄位建索引時，仍然受到 900 bytes 索引鍵大小限制。

Azure SQL Database 的常見假警報

在 Azure SQL Database 上，大量 DELETE 後看到儲存空間快滿了——這是假警報。空間根本沒被釋放。DELETE ... WITH (TABLOCK) 效果有限，必須搭配 TRUNCATE 或 ALTER INDEX ALL ON [TableName] REBUILD 才能真正回收。

---

2. 鎖定機制 — 你的交易真的安全嗎？

我曾經在生產環境遇過一個離奇的 Bug：兩筆交易同時更新同一張表，結果一筆交易讀到了另一筆還沒 COMMIT 的資料。這就是經典的 Dirty Read（髒讀）。

不使用適當鎖定機制時，會碰到三種資料異常：

異常類型	說明	情境
Dirty Read（髒讀）	讀到其他交易未提交的資料	T1 UPDATE 未 COMMIT，T2 SELECT 讀到修改後的值
Non-repeatable Read（不可重複讀）	同一交易內兩次讀取結果不同	T1 SELECT → T2 UPDATE COMMIT → T1 再次 SELECT 結果變了
Phantom Row（幻影列）	同一交易內多出新的資料列	T1 SELECT → T2 INSERT COMMIT → T1 再次 SELECT 多了一列

正確的做法：TABLOCKX 排他鎖

-- Problem: Without TABLOCKX, T2 can read T1's uncommitted changes
-- Session 1
BEGIN TRAN T1;
UPDATE Orders SET Amount = 999 WHERE OrderID = 1;
-- (not committed yet)

-- Session 2 (runs concurrently, sees Amount = 999 → Dirty Read!)
SELECT Amount FROM Orders WHERE OrderID = 1;

-- Solution: Use TABLOCKX for exclusive access
BEGIN TRAN T1;
SELECT * FROM Orders WITH (TABLOCKX) WHERE OrderID = 1;
-- Now T2 is BLOCKED until T1 commits or rolls back
UPDATE Orders SET Amount = 999 WHERE OrderID = 1;
COMMIT TRAN T1;

在高併發場景中，如果不想鎖整張表，也可以考慮設定交易隔離層級為 SERIALIZABLE 或使用 ROWLOCK, UPDLOCK 組合，但 TABLOCKX 是最簡單粗暴且確定有效的方式。

---

3. 查詢優化 — EXISTS vs IN 的效能陷阱

這個坑我踩了不止一次。先講結論：

• 正向查詢（EXISTS vs IN）：執行計畫幾乎相同，效能差異不大。
• 否定查詢（NOT EXISTS vs NOT IN）：NOT EXISTS 遠遠快於 NOT IN，差距可達數十倍。

-- NOT IN: Slow — performs O(n*m) comparison, NULL handling issues
SELECT * FROM Products
WHERE ProductID NOT IN (
    SELECT ProductID FROM OrderDetails
);

-- NOT EXISTS: Fast — uses semi-join, stops at first match
SELECT * FROM Products p
WHERE NOT EXISTS (
    SELECT 1 FROM OrderDetails od
    WHERE od.ProductID = p.ProductID
);

-- Additional trap: if OrderDetails.ProductID contains ANY NULL value,
-- NOT IN returns ZERO rows! NOT EXISTS handles NULL correctly.

NOT IN 之所以慢，是因為它必須對子查詢的每一筆結果做比對，而且還要處理 NULL 的三值邏輯。NOT EXISTS 則是用半連接（Semi-Join）策略，找到第一筆匹配就停止。

排序與 TOP 的隱藏陷阱

加上 TOP 之後，SQL Server 的排序演算法會完全改變。沒有 TOP 時用完整排序（Full Sort），有 TOP 時用 Top-N Sort，記憶體需求和執行路徑完全不同。

SQL 執行順序（必背）

很多查詢優化的問題，根源是不理解 SQL 的實際執行順序：

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP/OFFSET

注意 SELECT 在 WHERE 之後，所以你不能在 WHERE 中使用 SELECT 裡定義的別名。而 ORDER BY 在 SELECT 之後，所以可以用別名排序。理解這個順序，很多「為什麼這樣寫不行」的問題都迎刃而解。

另外，索引不只消除全表掃描，還能跳過排序階段。如果 ORDER BY 的欄位剛好有索引，SQL Server 可以直接按索引順序讀取，省掉排序的 CPU 和記憶體開銷。

---

4. 全文檢索 — 比 LIKE ‘%keyword%’ 快一百倍

如果你的應用有「搜尋文章內容」的需求，還在用 LIKE '%keyword%'，那你的查詢基本上每次都是全表掃描。全文檢索（Full-Text Search）透過反向索引（Inverted Index）來加速文字搜尋，效能差距是數量級的。

建立全文檢索的前提與步驟

前提：目標表必須有主鍵（Primary Key）。因為反向索引需要唯一識別碼來對應每筆資料。

-- Step 1: Enable full-text search on the database (if not already)
-- (SQL Server installs Full-Text Search as a feature)

-- Step 2: Create a full-text catalog
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

-- Step 3: Create a full-text index on the table
-- The table MUST have a primary key
CREATE FULLTEXT INDEX ON Articles (
    Title LANGUAGE 1028,      -- 1028 = Traditional Chinese
    Content LANGUAGE 1028
)
KEY INDEX PK_Articles          -- Must reference the PK
ON ftCatalog
WITH CHANGE_TRACKING AUTO;     -- Auto-update when data changes

-- Step 4: Query using CONTAINS or FREETEXT
SELECT * FROM Articles
WHERE CONTAINS(Content, N'效能優化');

-- Compare with LIKE (full table scan every time)
SELECT * FROM Articles
WHERE Content LIKE N'%效能優化%';

語言設定很重要：LANGUAGE 1028（繁體中文）會使用對應的斷詞器（Word Breaker），直接影響搜尋品質。英文斷詞用空格就行，但中文斷詞需要語意分析，設錯語言會導致搜不到結果。

---

5. 監控與追蹤 — 沒有 Profiler 怎麼辦？

SQL Server Profiler 在生產環境不一定能用（效能開銷太大，或者根本沒權限）。這時候 DMV（Dynamic Management Views）就是你的救星。

追蹤特定時間範圍的查詢

-- Find top queries by CPU time within a time range
SELECT TOP 20
    qs.last_execution_time,
    qs.execution_count,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_elapsed_time / 1000 AS total_elapsed_ms,
    qs.total_logical_reads,
    SUBSTRING(st.text,
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1
    ) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.last_execution_time >= '2026-04-04 09:00:00'
  AND qs.last_execution_time <= '2026-04-04 18:00:00'
ORDER BY qs.total_worker_time DESC;

查看當前執行中的程序

-- Quick check: who's running what right now?
EXEC sp_who2;

-- Or with more detail via DMV
SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    t.text AS query_text,
    r.cpu_time,
    r.reads,
    r.writes
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50; -- Exclude system sessions

全庫儲存空間盤點

-- Iterate all user tables and check space usage
CREATE TABLE #SpaceUsed (
    TableName NVARCHAR(128),
    Rows NVARCHAR(20),
    Reserved NVARCHAR(20),
    Data NVARCHAR(20),
    IndexSize NVARCHAR(20),
    Unused NVARCHAR(20)
);

DECLARE @tbl NVARCHAR(128);
DECLARE tbl_cursor CURSOR FOR
    SELECT TABLE_SCHEMA + '.' + TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE';

OPEN tbl_cursor;
FETCH NEXT FROM tbl_cursor INTO @tbl;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #SpaceUsed
    EXEC sp_spaceused @tbl;
    FETCH NEXT FROM tbl_cursor INTO @tbl;
END

CLOSE tbl_cursor;
DEALLOCATE tbl_cursor;

SELECT * FROM #SpaceUsed ORDER BY CAST(REPLACE(Reserved, ' KB', '') AS BIGINT) DESC;
DROP TABLE #SpaceUsed;

---

6. 暫存表大全 — @、#、## 到底差在哪？

SQL Server 有三種暫存表，看起來差一個符號，但行為天差地別：

特性	@TableVar（表變數）	#TempTable（本地暫存表）	##GlobalTemp（全域暫存表）
儲存位置	記憶體（小量時）/ tempdb	tempdb	tempdb
統計資訊	無（優化器假設 1 列）	有	有
作用範圍	當前批次/程序	當前 Session	所有 Session
交易回滾	不受 ROLLBACK 影響	受 ROLLBACK 影響	受 ROLLBACK 影響
可建索引	僅限宣告時的約束	可隨時建立	可隨時建立
適用場景	少量資料（< 100 列）	大量資料、需要索引	跨 Session 共享資料

選擇指南

• 資料量 < 100 列，且不需要索引 → 用 @TableVar
• 資料量大，需要統計資訊讓優化器做正確決策 → 用 #TempTable
• 需要跨 Session 共享（例如 ETL 中間結果） → 用 ##GlobalTemp（但要小心生命週期管理）
• 在 ROLLBACK 時需要保留資料（例如錯誤日誌） → 用 @TableVar，因為它不受交易回滾影響

-- Table variable: optimizer always estimates 1 row
DECLARE @small TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @small SELECT TOP 10 ID, Name FROM Products;

-- Local temp table: has statistics, better for large datasets
CREATE TABLE #bigtemp (ID INT, Name NVARCHAR(50));
INSERT INTO #bigtemp SELECT ID, Name FROM Products;
CREATE INDEX IX_bigtemp_ID ON #bigtemp(ID); -- Can add indexes

-- Global temp table: visible to all sessions
CREATE TABLE ##shared (ID INT, Name NVARCHAR(50));
-- Other sessions can SELECT from ##shared

最常見的錯誤是：把幾萬筆資料塞進 @TableVar，然後納悶為什麼 JOIN 超慢。原因是優化器認為裡面只有 1 列，選了 Nested Loop 而不是 Hash Join。換成 #TempTable 就正常了。

---

結語

SQL Server 的這些坑，有的文件上有寫但沒人看，有的要自己測過才會懂。我把這幾年的經驗整理在這裡，希望能幫到正在跟 SQL Server 搏鬥的你。如果只能記住一件事，請記住：NOT IN 是效能炸彈，永遠用 NOT EXISTS 取代它。這一個改動可能就能讓你的查詢從分鐘級變成秒級。

---

常見問題 FAQ

Q: DELETE 後空間沒有減少，是 Bug 嗎？

不是 Bug，這是 SQL Server 的設計。DELETE 只標記刪除，頁面仍歸屬於表。使用 TRUNCATE TABLE 或 ALTER INDEX REBUILD 來真正回收空間。

Q: NOT IN 和 NOT EXISTS 結果一樣嗎？

不一定。如果子查詢包含 NULL 值，NOT IN 會返回空結果集（因為 NULL 的比較結果是 UNKNOWN）。NOT EXISTS 則能正確處理 NULL。除了效能差異，正確性也是選擇 NOT EXISTS 的原因。

Q: 什麼時候該用表變數 @，什麼時候用暫存表 #？

簡單判斷：資料量少於 100 列用 @，超過就用 #。關鍵差異在於統計資訊——表變數沒有統計資訊，優化器會做出錯誤的執行計畫。

Q: 全文檢索和 LIKE 差多少？

在百萬筆資料的文字欄位上，全文檢索可以比 LIKE '%keyword%' 快 100 倍以上。LIKE 前綴帶 % 時無法使用索引，只能全表掃描；全文檢索則使用反向索引，直接定位包含關鍵字的列。

Q: 生產環境不能用 SQL Profiler 該怎麼監控？

使用 DMV（動態管理檢視）：sys.dm_exec_query_stats 搭配 sys.dm_exec_sql_text 可以按時間範圍追蹤查詢，sys.dm_exec_requests 可以看當前正在執行的查詢，效能衝擊遠小於 Profiler。