萬盛學電腦網

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

SQL Server 查詢優化 13 大黃金法則:從索引到架構的系統性提速

查詢慢?按這 13 條清單逐項排查,覆蓋 90% 效能問題

SQL Server 查詢效能優化是系統工程,涉及索引、I/O、記憶體、鎖、網路、計畫品質等多個維度。本文將最常見的 13 大致因與對策系統化整理。

致因清單與對策對照表

#常見致因核心對策驗證指標
1缺失索引或索引未被使用依查詢負載建立覆蓋索引;檢查 sys.dm_db_missing_index_detailsExecution Plan: Table Scan / Key Lookup
2I/O 吞吐瓶頸資料/日誌/Tempdb 分離不同磁碟陣列;RAID 10/SSD;啟用瞬間檔案初始化PAGEIOLATCH_* 等待、Avg. Disk Sec/Read > 10ms
3缺乏計算欄/持久化計算欄將常用運算式 (如 Price*Qty) 定義為 PERSISTED 計算欄並建索引Scalar Operator 成本高、CPU 壓力大
4記憶體不足設定 max server memory;增加物理 RAM;檢查 Target vs Total Server MemoryPAGE_LATCH、Memory Grants Pending、PLE < 300s
5網路延遲/頻寬應用與資料庫同機房/同 VPC;啟用連接池;減少往返 (批次、TVP)ASYNC_NETWORK_IO 等待、Client 端耗時 >> Server 端
6結果集過大分頁 (OFFSET/FETCH)、增量同步、資料歸檔、只選需要欄位Rows Returned >> Rows Needed、網路流量異常
7鎖/死鎖縮短交易、固定存取順序、ROWLOCK/UPDLOCK、啟用 RCSILCK_M_* 等待、Deadlock Graph (Trace 1222/Extended Events)
8活躍會話阻塞鏈sp_who2/sp_lock/DMV 定位 Head Blocker;KILL 或優化其查詢Blocking Session ID 鏈、等待時間累積
9回傳不必要行/列SELECT * → 明確列清單;WHERE 過濾下推;EXISTS 替代 COUNT網路流量、Client 記憶體、Plan Output List
10查詢寫法不佳/計畫劣化參數化、避免函數包裹索引欄、UPDATE STATISTICS、強制計畫 (Plan Guide/Query Store)Plan Cache 重編譯率、Parameter Sniffing 症狀
11LIKE 模糊查詢無法用索引LIKE 'abc%' 可用索引;LIKE '%abc' 需全文索引/反向索引/計算欄全文索引、計算欄 REVERSE(Col) + 索引
12OLTP/OLAP 混跑讀寫分離 (AlwaysOn Secondary/複寫/ETL 到數據倉庫)長時間查詢阻塞 OLTP、Tempdb 爭用
13分散式分區檢視表水平分區表 + 分散式分區檢視表 實現伺服器聯邦 (Scale-Out)單機寫入瓶頸、地理分散

深度優化技巧精選

I/O 隔離:三檔分三盤

  • 資料檔 (.mdf/.ndf) → RAID 10 / NVMe 陣列 A
  • 日誌檔 (.ldf) → RAID 1 / NVMe 陣列 B (順序寫優先)
  • Tempdb → 獨立 SSD/NVMe 陣列 C (多檔案 1:1 核心數)

索引填充因子策略

  • 高插入表:FILLFACTOR = 80-90 預留空間減少頁分裂。
  • 唯讀/歸檔表:FILLFACTOR = 100 最大密度。
  • 定期 ALTER INDEX ... REBUILD WITH (FILLFACTOR = ...) 或維護計畫重組。

記憶體配置黃金法則

-- 維護計畫:每日 UPDATE STATISTICS WITH FULLSCAN 核心表
-- 或啟用 AUTO_UPDATE_STATISTICS_ASYNC ON (非同步更新,避免查詢等待)

T-SQL 寫法優化速查

  • EXISTS > IN > JOIN 用於半連接 (僅判斷存在)。
  • 避免隱含轉換:WHERE VarcharCol = 123 → 索引失效,改 WHERE VarcharCol = '123'。
  • 動態 SQL 參數化:sp_executesql @sql, N'@id INT', @id 重用計畫。
  • 交易範圍最小化:不在交易中做網路呼叫、複雜計算、等待使用者輸入。
  • COMMIT/ROLLBACK:COMMIT 提交當前交易;ROLLBACK 回滾**整個巢狀交易** (含外層)。動態 SQL 交易包裹在外層:BEGIN TRAN; EXEC(@sql); COMMIT;。
copyright © 萬盛學電腦網 all rights reserved