SQL Server 效能調教

TLDR

透過 SSMS 的「實際評估計畫」與「估計執行計畫」可識別效能瓶頸與遺漏索引。
建立索引時應整合相似需求，避免過多索引導致寫入效能下降。
叢集索引決定物理排序，非叢集索引則作為二級索引使用。
使用 INCLUDE 可將非鍵欄位納入索引，減少資料頁面讀取。
複合索引必須遵循「左前綴規則」，否則無法有效利用索引。
處理鎖定衝突可視需求選擇 NOLOCK、NOWAIT 或 READPAST。
Parameter Sniffing 可透過 Query Store 強制計畫、OPTIMIZE FOR、RECOMPILE 或清除計畫快取來解決。

使用 SSMS 執行計畫工具分析查詢

在調整查詢效能時，可利用 SSMS 的執行計畫工具進行診斷。

執行計畫模式差異

包括實際評估計畫：顯示查詢執行後的實際統計資訊（如耗時、讀取量），適合用於優化已執行過的查詢。
顯示估計執行計畫：根據統計資料預估執行路徑，無需實際執行查詢，適合分析未執行或執行時間過長的查詢。

識別並補上遺漏索引

當查詢效能不佳時，可對執行計畫按右鍵選擇「遺漏索引詳細資訊」。系統會自動產生建議的 CREATE INDEX 語法。

WARNING

請勿盲目建立所有建議的索引。許多索引需求是可以整合的，過多的索引會顯著降低資料寫入效能。

若需從系統檢視表找出遺漏索引，可使用以下查詢：

sql

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' +
        CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' +
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;

SQL Server 索引基礎

索引基於 B-Tree 結構，分為叢集索引（Clustered Index）與非叢集索引（Non-Clustered Index）。

叢集索引：決定資料的物理排序，每個資料表僅能有一個。不建議使用 GUID 作為鍵值，流水號較為合適。
非叢集索引：作為二級索引，包含鍵值與指向叢集索引的指標。
INCLUDE 用途：在非叢集索引中包含非鍵欄位，可減少從資料頁面讀取資料的次數，提升查詢效率。
左前綴規則：複合索引僅在查詢條件包含索引最左側連續欄位時有效。若查詢條件不符合此規則，SQL Server 最佳化器通常不會使用該索引。

查詢鎖定處理方式

當查詢因鎖定而阻塞時，可根據業務需求選擇處理策略：

NoLock：忽略鎖定直接讀取，可能讀取到未提交的資料（髒讀）。
NoWait：不等待鎖定釋放，立即返回錯誤。
ReadPast：跳過已被鎖定的資料列，只讀取可用的資料。

Parameter Sniffing 問題與解決

Parameter Sniffing 指資料庫根據首次執行的參數值產生執行計畫，若該參數值具備極端特性，後續使用不同參數時效能將大幅下降。

效能問題辨識

若參數化查詢執行緩慢，但改為非參數化查詢（直接帶入值）後效能恢復正常，則極可能受此影響。

解決方案

Query Store：SQL Server 2016+ 建議方案，可透過「強制計劃 (Force Plan)」固定效能穩定的執行計畫。
OPTION (OPTIMIZE FOR)：強制優化器針對特定值或平均分佈（UNKNOWN）產生計畫。
OPTION (RECOMPILE)：每次執行皆重新編譯，適合低頻率但變異大的複雜查詢。
清除計畫快取：使用 DBCC FREEPROCCACHE 移除舊計畫，強制重新產生。

若要找出潛在的 Parameter Sniffing 查詢，可分析執行時間變異：

sql

SELECT TOP 20
    t.text AS [SQL Text],
    st.execution_count,
    [Max Elapsed (ms)] = st.max_elapsed_time / 1000,
    [Avg Elapsed (ms)] = (st.total_elapsed_time / st.execution_count) / 1000,
    [Max/Avg Ratio] = CAST(st.max_elapsed_time * 1.0 / NULLIF(st.total_elapsed_time / st.execution_count, 0) AS DECIMAL(10,2)),
    st.plan_handle,
    st.last_execution_time
FROM sys.dm_exec_query_stats AS st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) AS t
WHERE t.dbid = DB_ID('{YourDatabaseName}')
  AND st.execution_count > 50
ORDER BY [Max/Avg Ratio] DESC;

異動歷程

2023-03-15 初版文件建立。
2026-01-01
- 修正 Parameter Sniffing 檢測腳本中判定邏輯與預期相反的錯誤。
- 增加 Parameter Sniffing 的其他解決方案。

筆記目錄

SQL Server 效能調教 ​

TLDR ​

使用 SSMS 執行計畫工具分析查詢 ​

執行計畫模式差異 ​

識別並補上遺漏索引 ​

SQL Server 索引基礎 ​

查詢鎖定處理方式 ​

Parameter Sniffing 問題與解決 ​

效能問題辨識 ​

解決方案 ​

異動歷程 ​