SQL Server 踩坑實錄：從 DELETE 不釋放空間到 NOT IN 效能炸彈
這篇文章是我從 DBA 到全端架構師這幾年，在 SQL Server 效能優化上踩過的坑的總整理。不是教科書式的理論，而是每一條都是我實際測試、實際踩雷後的血淚經驗。如果你正在處理 SQL Server 效能優化的問題——DELETE 後空間沒釋放、查詢莫名其妙變慢、鎖定機制搞不清楚——這篇應該能幫你少走不少冤枉路。
TL;DR 重點摘要
- DELETE 不會釋放磁碟空間，只是標記刪除。要真正回收空間，必須用 TRUNCATE 或 ALTER INDEX REBUILD。
- NOT IN 是效能炸彈，改用 NOT EXISTS 可以讓查詢快數十倍，尤其在子查詢結果集大的時候。
- 沒有 TABLOCKX 的交易不安全，並行交易會讀到未提交的資料（Dirty Read），高併發場景務必設定正確的隔離層級。
- 暫存表不是都一樣，@ 表變數、# 本地暫存表、## 全域暫存表各有適用場景，選錯會嚴重影響效能。
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。
