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

SQL Server 索引缺失診斷：DMV 深度實戰與 DTA 輔助優化

編輯：關於SqlServer

為何需要 DMV？

面對複雜查詢，人工判斷最佳索引組合極難。SQL Server 2005+ 引入 Missing Index DMV，優化器在編譯階段自動評估「若有此索引，成本能降多少」，為 DBA 提供數據驅動的建議。

核心 DMV 架構

DMV / 函數	用途	關鍵列
`sys.dm_db_missing_index_details`	缺失索引細節（表、等值/不等值欄位、包含欄位）	`index_handle`, `equality_columns`, `inequality_columns`, `included_columns`, `statement`
`sys.dm_db_missing_index_groups`	缺失索引分組	`index_group_handle`, `index_handle`
`sys.dm_db_missing_index_group_stats`	組級統計：預估改善幅度、查詢次數、平均成本	`group_handle`, `avg_user_impact`, `user_seeks`, `user_scans`, `avg_total_user_cost`
`sys.dm_db_missing_index_columns(index_handle)`	函數：返回某缺失索引的欄位清單	`column_name`, `column_usage` (EQUALITY/INEQUALITY/INCLUDE)

實戰查詢：Top 10 高收益缺失索引

SELECT TOP 10
    migs.group_handle,
    mid.statement AS [Table],
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_user_impact AS [Avg_Impact_%],
    migs.user_seeks + migs.user_scans AS [Total_Ops],
    'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.', '_') 
        + ' 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_Statement]
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig 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_user_impact DESC;

輸出範例與建索引

Table: [dbo].[SalesOrderDetail_test]
equality_columns: ProductID
included_columns: SalesOrderID
→ CREATE INDEX IX_SalesOrderDetail_test_ProductID_INC_SalesOrderID 
   ON [dbo].[SalesOrderDetail_test] (ProductID) INCLUDE (SalesOrderID);

關鍵指標解讀

avg_user_impact：預估查詢成本下降百分比（如 95.2 = 95.2%）。
user_seeks / user_scans：潛在受益查詢次數，越高優先級越高。
avg_total_user_cost：單次查詢平均成本，配合次數算總收益。

⚠️ 必讀限制

非持久化：服務重啟即清空，需定期導出備份（如 Agent Job 寫入歷史表）。
單語句視角：建議基於單查詢，未考慮寫入開銷、鎖競爭、存儲空間、其他查詢負面影響。
不覆蓋參數嗅探/統計過期導致的計畫問題。

輔助工具：DTA (Database Engine Tuning Advisor)

輸入工作負載（Trace / Plan Cache / SQL 檔），輸出建議索引、統計、分區策略。
可模擬「假設索引」評估綜合收益，比單純 DMV 更全面。
建議：先跑 DMV 找顯著缺失，再用 DTA 做綜合調優。

自動化落地建議

-- Agent Job 每日 02:00 執行
INSERT INTO dba.missing_index_history (capture_time, database_id, ... )
SELECT GETDATE(), database_id, ... FROM sys.dm_db_missing_index_details
WHERE database_id = DB_ID();

結合歷史趨勢，識別「持續出現」的缺失索引，才是真正值得建立的高價值索引。