SQL Server 2008 全文檢索效能陷阱：架構變更導致 StackOverflow 棄用內建方案

背景：全文檢索架構大變革

SQL Server 2005 及之前：全文檢索為外部服務（Microsoft Search Service），資料需跨程序傳輸至外部索引器，備份/還原不包含全文目錄，記憶體/CPU 無法與資料庫引擎協調。

SQL Server 2008：全文檢索內建於資料庫引擎，共享緩衝池、排程器、鎖管理器，支援隨資料庫備份/還原，資源動態平衡。

實戰災難：StackOverflow 案例

症狀

• 全文查詢常見關鍵字（如 "SQL"）匹配 50,000-100,000 筆文件。
查詢計畫顯示 50k-100k 邏輯讀取。
• 同時面臨大量寫入（新增修訂版本），導致嚴重鎖爭用與交易日誌暴增。

根因分析

1. ACID 強制鎖定：全文查詢在交易式資料庫中需遵守隔離等級，掃描大量文件時持有大量共享鎖，阻塞寫入。
2. 內建架構副作用：共享資源意味著全文查詢與 OLTP 工作負載直接競爭緩衝池、CPU、鎖管理器。
3. 查詢計畫最佳化不足：全文搜尋無法有效利用傳統索引提示，難以限制掃描範圍。

StackOverflow 決策

Brent Ozar（團隊 DBA）與 SQL Server 產品組合作測試後結論：架構根本限制無法在可接受時間內修復。團隊規劃遷移至 Lucene.NET（後來演進為 Elasticsearch），徹底移除內建全文檢索依賴。

遷移替代方案比較

若必須繼續使用內建全文檢索的緩解措施

1. 啟用讀取認可快照隔離 (RCSI)：ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON，讀不阻寫、寫不阻讀。
2. 限制全文查詢範圍：結合結構化欄位（日期、分類）先過濾，再套用 CONTAINS/FREETEXT。
3. 調整填滿因子與統計更新：全文目錄重建時設定 FILLFACTOR，定期 UPDATE STATISTICS。
4. 非同步填入：ALTER FULLTEXT INDEX SET CHANGE_TRACKING AUTO（非同步）而非即時更新。
5. 硬體隔離：將 tempdb、全文目錄放置於獨立 SSD，減輕 I/O 爭用。

升級驗證清單

從 2005 升級 2008 後，務必執行完整負載測試：

• 模擬高併發寫入 + 熱門關鍵字全文查詢
• 監控 sys.dm_tran_locks、sys.dm_os_wait_stats（LCK_M_S、FT_IFTS_SCHEDULER_IDLE_WAIT）
• 比對查詢延遲、吞吐量與 2005 基準

結語

SQL Server 2008 全文檢索架構雖理論優雅，但在「高寫入 + 高頻熱門詞查詢」混合負載下暴露資源共享的致命缺陷。StackOverflow 案例證明：對搜尋為核心業務的系統，專用搜尋引擎仍是正解。升級前請三思業務負載特性，必要時規劃異構搜尋架構。