案例背景
- 兩個大型 Web 站點採雙伺服器負載平衡。
- 各 Web 伺服器獨立使用 ASP.NET 內建記憶體快取（In-Memory Cache）。
- 快取未共享，導致相同資料重複查詢 DB，連線數從正常 ~250 飆升至 740+。
- 前端大量請求逾時，DB CPU 與等待統計顯著上升。
瓶頸分析
觀測發現：
- 連線數 < 400 時，回應流暢；> 400 即出現大量
ASYNC_NETWORK_IO、CXPACKET、LCK_M_* 等待。
- 硬體雖強（多核心、大記憶體、SSD），但連線池溢位、Thread Pool 耗盡、鎖競爭成為系統吞吐上限。
- 根因非 DB 參數設定，而是應用層快取架構缺失。
緊急應變措施（治標）
- 啟用 ASP.NET 輸出快取：在高流量頁面（如首頁、列表頁）加入
<%@ OutputCache Duration="60" VaryByParam="none" %>，大幅降低 DB 查詢頻率。
- 啟用鏡像備援資料庫分流讀取：將部分唯讀查詢導向鏡像夥伴（不同機房），分散主庫壓力，兼具災備與讀寫分離效果。
上線後連線數降至 350 左右，前端逾時現象消失。
根本解決：導入分散式快取
長期必須將 In-Memory Cache 替換為 分散式快取（如 Memcached、Redis、NCache、AppFabric）：
- 所有 Web 節點共享同一快取叢集，資料只查一次 DB。
- 支援快取相依性、標籤式失效、高可用性叢集模式。
- 搭配
SqlCacheDependency 或主動失效機制，確保資料一致性。
SQL Server 端輔助優化建議
| 設定/動作 | 說明 |
|---|
| Max Degree of Parallelism (MAXDOP) | 依核心數設定（建議 ≤ 8），避免過度平行化耗盡 Worker Thread。 |
| Cost Threshold for Parallelism | 調高至 50，減少小查詢並行化開銷。 |
| 啟用 Query Store (2016+) | 追蹤高耗查詢計畫變更，快速定位退化。 |
| 檢視 sys.dm_exec_connections / sessions | 定期稽核連線來源、登入、等待類型，找出洩漏或異常模式。 |
| 連線池大小 | 應用端 Max Pool Size 設定合理值（預設 100），避免單一應用佔滿。 |
經驗總結
- 連線數暴增通常是應用架構問題而非 DB 參數問題。
- 負載平衡環境必須共享快取，否則 DB 連線數會隨節點數線性增長。
- 鏡像/可用性群組次要複本可作讀取分流，但需評估同步延遲對業務影響。
- 建立連線數基線與警示（如 > 500 發送告警），做到早發現早處理。
