識別真實索引與自動統計資料：INDEXPROPERTY 與系統目錄查詢實務 (補充版)

本文補充 INDEXPROPERTY 函數與系統目錄查詢在 SQL Server 2005/2008/2012+ 版本上的完整用法，並說明如何將自動統計轉化為索引優化線索。

三種識別方法完整語法

1. 命名慣例：_WA_Sys__ (所有版本相容)。
2. INDEXPROPERTY：

SELECT INDEXPROPERTY(OBJECT_ID('dbo.Orders'), '_WA_Sys_OrderID_123456', 'IsAutoStatistics');
-- 回傳 1 = 自動統計，0 = 使用者索引

3. sys.indexes 目錄檢視 (推薦，功能最全)：

SELECT
    OBJECT_NAME(object_id) AS TableName,
    name AS IndexName,
    type_desc,
    is_primary_key,
    is_unique,
    is_hypothetical,          -- 由 DTA 建立的假設索引
    auto_created              -- TRUE = 自動統計 (SQL 2005+)
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Orders');

auto_created = 1 直接標示自動統計，**比命名慣例更可靠**。

統計資料清單查詢 (含自動/手動區分)

SELECT
    OBJECT_NAME(sp.object_id) AS TableName,
    s.name AS StatsName,
    s.auto_created,
    s.user_created,
    STATS_DATE(s.object_id, s.stats_id) AS LastUpdated,
    sp.rows, sp.rows_sampled, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY s.auto_created DESC, sp.modification_counter DESC;

自動統計 → 缺失索引線索工作流

1. 定期執行上述查詢，篩選 auto_created = 1 且 modification_counter 高 (頻繁查詢) 的統計。
2. 對照 sys.dm_db_missing_index_details 確認最佳化器建議索引欄位是否一致。
3. 評估選擇性、查詢頻率、寫入開銷，決定是否建立實體索引。
4. 建立索引後，**手動刪除對應自動統計** (DROP STATISTICS Table._WA_Sys_...) 避免重複維護開銷。

版本差異提醒

• SQL 2000 僅有 sysindexes、INDEXPROPERTY、_WA_Sys_ 命名慣例。
• SQL 2005+ 引入 sys.indexes、sys.stats、auto_created 欄位、dm_db_stats_properties DMV。
• SQL 2012+ 支援 dm_db_missing_index_* 完整缺失索引建議。

結語

善用 sys.indexes.auto_created 與 dm_db_stats_properties 精準識別自動統計，配合缺失索引 DMV 形成「自動統計 → 缺失索引建議 → 評估建立 → 清理統計」閉環，持續優化索引策略。