SQL Server 自動偵測與建立 Missing Index：DMV 查詢與 DDL 生成腳本

SQL Server 查詢最佳化器在編譯過程中會記錄「若有此索引可改善查詢」的建議，儲存於 Missing Index 相關 DMV。本文提供完整查詢腳本，計算改善度量並自動生成 CREATE INDEX DDL，輔助索引調優決策。

核心查詢：含改善度量與 DDL 生成

SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX [missing_index_' 
        + CONVERT(VARCHAR, mig.index_group_handle) + '_' 
        + CONVERT(VARCHAR, mid.index_handle) + '_' 
        + LEFT(PARSENAME(mid.statement, 1), 32) + ']'
    + ' 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,
    migs.*, mid.database_id, mid.[object_id]
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 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY improvement_measure DESC;

關鍵欄位解讀

• improvement_measure：綜合評分 = 平均查詢成本 × 平均改善% × (搜尋+掃描次數)。越高代表潛在收益越大。
• equality_columns：WHERE JOIN ON 中等值比較欄位（適合放索引鍵前段）。
• inequality_columns：範圍比較欄位（<、>、BETWEEN），適合放索引鍵後段。
• included_columns：SELECT 清單中非條件欄位，放 INCLUDE 實現覆蓋索引。
• mid.statement：完整物件名稱 [db].[schema].[table]。

使用注意事項（必讀）

1. 非即時生效：DMV 內容隨服務重啟重置，且僅記錄編譯時的建議，非執行時統計。
2. 不考慮現有索引：可能建議重複或近似索引，需人工比對 sys.indexes 去重。
3. 不評估維護成本：寫入負載高的表新增索引可能得不償失。
4. 欄位順序參考：等值欄位優先、區分度高者優先、窄欄位優先。
5. 建議門檻：improvement_measure > 10 為經驗值，視系統負載調整。

最佳實務流程

1. 定期（每日/每週）擷取上述查詢結果存入歷史表。
2. 人工審核 Top N 建議，對照現有索引、寫入頻率、查詢模式。
3. 在非尖峰時段於測試環境套用並驗證執行計畫改善。
4. 上線後持續監控 sys.dm_db_index_usage_stats 確認新索引被使用。