您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

善用 SQL Server 動態管理檢視 (DMV) 精準發現缺失索引

編輯：關於SqlServer

SQL Server 2005 引入的「缺失索引 DMV 群組」，讓 DBA 無需手動分析查詢計畫、搜尋 Table Scan，即可直接從引擎收集的工作負載統計中，獲得具體的索引建議（含欄位、預估效益、使用頻率）。本文系統介紹四大核心 DMV、聯查範本、結果解讀與實務限制。

四大核心 DMV 架構圖

sys.dm_db_missing_index_groups (群組統計)
       │ 1:N
       ▼
sys.dm_db_missing_index_group_stats (群組效益統計: user_seeks, avg_total_user_cost, avg_user_impact)
       │ 1:1
       ▼
sys.dm_db_missing_index_details (索引定義: equality_columns, inequality_columns, included_columns)
       │ 1:N
       ▼
sys.dm_db_missing_index_columns(index_handle) (索引欄位清單: column_name, column_usage)

關鍵欄位解讀

DMV	核心欄位	業務含義
missing_index_details	`equality_columns`	WHERE/JOIN/GROUP BY 中出現「等值比較 (=)」的欄位 → 最適合做索引鍵 (Key Columns)
	`inequality_columns`	出現「範圍比較 (<, >, BETWEEN, LIKE)」的欄位 → 適合做索引鍵次序靠後，或不納入
	`included_columns`	SELECT 清單中需回表的欄位 → 適合做 INCLUDE 非鍵欄位，避免 Key Lookup
missing_index_group_stats	`user_seeks`	此索引若存在，預估可轉為 Seek 的查詢次數（越高價值越大）
	`avg_total_user_cost`	當前查詢平均成本（無索引時的代價）
	`avg_user_impact`	預估效能提升百分比（如 95.2 代表建立後查詢成本降 95.2%）

一鍵聯查腳本：直接產出 CREATE INDEX 草稿

SELECT 
    'CREATE NONCLUSTERED INDEX IX_' + 
    REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.', '_') + '_' + 
    CAST(mig.index_group_handle AS varchar(10)) + '_' + 
    CAST(mid.index_handle AS varchar(10)) + 
    ' 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, '') + ')' +
    CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END +
    ';' AS CreateIndexStatement,
    migs.user_seeks,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
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
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * migs.user_seeks DESC;

實戰案例：AdventureWorks.Person.Address

執行查詢：

SELECT City, StateProvinceID, PostalCode 
FROM Person.Address 
WHERE StateProvinceID = 1;

DMV 回報：

equality_columns = [StateProvinceID] → 等值過濾，極適合做 Key
inequality_columns = NULL
included_columns = [City], [PostalCode] → 覆蓋查詢，避免 Key Lookup
user_seeks = 1, avg_user_impact = 99.12

建議索引：

CREATE NONCLUSTERED INDEX IX_Address_StateProvinceID 
ON Person.Address (StateProvinceID) 
INCLUDE (City, PostalCode);

必知限制與維護策略

重啟即歸零： DMV 統計隨 SQL Server 服務重啟清空。生產環境建議建立定期持久化作業（如每小時寫入歷史表），避免重啟後盲區。
DDL 會清除該表統計： 對目標表執行 CREATE/DROP INDEX、ALTER TABLE 等結構變更，會清除該表在 DMV 中的所有累積統計。
不支援 Partitioned Table / Indexed View / XML 索引 的建議。
不考慮寫入負擔： DMV 僅從「讀取受益」角度建議，若表寫入極頻繁，新增索引可能導致整體吞吐下降。決策前必須評估 sys.dm_db_index_usage_stats 中的 user_updates。
僅針對單表： 無法發現跨表 Join 缺失索引、外鍵缺失索引等關聯問題。