SQL Server 識別真實索引與自動建立統計資料的實用技巧

sys.indexes / sysindexes 中混雜使用者建立的索引與查詢最佳化器自動建立的統計資料 (名稱前綴 _WA_Sys_)。區分兩者並利用統計資料反推缺失索引，是效能調優的實用技能。

識別方法

1. 命名慣例：自動統計名稱固定為 _WA_Sys_<欄位>_<雜湊> (SQL 7.0/2000/2005/2008 相容)。
2. INDEXPROPERTY 函數：INDEXPROPERTY(object_id, index_name, 'IsAutoStatistics') 回傳 1 即為自動統計。
3. 系統目錄查詢：

SELECT
    OBJECT_NAME(id) AS TableName,
    COUNT(*) AS AutoStatsCount
FROM sysindexes
WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1
  AND INDEXPROPERTY(id, name, 'IsAutoStatistics') = 1
GROUP BY OBJECT_NAME(id)
ORDER BY COUNT(*) DESC;

統計資料即缺失索引的線索

• 最佳化器在欄位無索引時自動建立統計，以評估選擇性。
• 大量自動統計 (> 20) 往往暗示該資料表索引策略嚴重不足，應優先檢視 WHERE/JOIN/GROUP BY 欄位補建索引。

實作範例：Northwind Orders 表

USE tempdb;
SELECT * INTO Orders FROM northwind..Orders;
SELECT * FROM Orders WHERE OrderID = 10248;  -- 觸發自動統計建立
SELECT name FROM sysindexes WHERE id = OBJECT_ID('Orders') AND name LIKE '_WA_Sys%';
-- 輸出 _WA_Sys_OrderID_xxx 表示 OrderID 缺乏索引

注意事項

• 並非所有自動統計皆需建立索引 (如低選擇性欄位、極少查詢)。
• 定期清理無用自動統計：DROP STATISTICS TableName._WA_Sys_...。
• 啟用 AUTO_CREATE_STATISTICS (預設 ON) 確保最佳化器有基本統計可用。

結語

善用 IsAutoStatistics 屬性與命名慣例快速篩選自動統計，將其視為「隱性索引需求清單」，配合查詢負載分析精準補建索引，事半功倍。