[SQL Server] Disabled Index still update Statistics, 停用索引 仍會更新 統計值

在維護大型系統上，需要定期評估 Index 適用性。
若判斷無須使用該 Index，則執行 Disabled Index 與進一步觀察對系統的影響程度。

提醒

• 若經確認無需使用該 Index 後，應移除該 Index。

Disabled Index 會防止使用者存取 Index，Query Optimizer 不使用此 Index。但 SQL Server 仍會維護與更新該 Index 的統計值。


Disabled Index 仍會更新 Statistics

---

Disabled Index still update Statistics, 停用索引 仍會更新 統計值




01. 使用 sp_helpindex 觀察資料表 [Person].[Address] 的 Index 資訊

-- figure 01_Index





02. 使用 DMV 與 STATS_DATE 觀察 全部資料表的 Index 與 Statistics 資訊

Get all indexes and Statistics last updated datetime

-- figure 02_Get all indexes and Statistics last updated datetime





03. 停用 Index

• Disable Nonclustered Index
• IX_Address_StateProvinceID

-- figure 11_Disable Nonclustered Index






04. 觀察 資料表的 Index 與 Statistics 資訊

• 確認 IX_Address_StateProvinceID 已經被 Disabled

-- figure 12_Get all indexes and Statistics last updated datetime





05. 更新 Statistics

Update the Statistics of table

-- figure 21_Update the Statistics of table





06. 觀察 Statistics 資訊

• 確認 Statistics 已經被更新
• 即便該 Index 早已被 Disabled

-- figure 22_Get all indexes and Statistics last updated datetime





07. 使用 DBCC SHOW_STATISTICS 觀察 Statistics

• 確認已經被更新

-- figure 31_DBCC SHOW_STATISTICS





08. 使用 DMV: dm_db_stats_properties 觀察 Statistics

• 確認已經被更新

-- figure 32_dm_db_stats_properties





Disabled Index 會防止使用者存取 Index，Query Optimizer 不使用此 Index。
但 SQL Server 仍會維護與更新該 Index 的統計值。


Disabled Index

• Disabled Index 會防止使用者存取 Index ，而 Disabled Clustered Index 則會防止存取基礎資料表資料。 
• 索引定義會保留在中繼資料內，而 index statistics 資料會保留在 Nonclustered Indexes上。 
• 停用 View 上的 非叢集 或 叢集索引，實際上會刪除 Index Data。 
• Disabled Clustered Index，則會防止存取資料；這些資料仍留在資料表中，但無法用於資料操作語言 (DML) 作業，除非 卸除 或 重建索引。

---

Sample Code

20180818_Disabled_Index_Update_Statistics
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

Reference

Disable Indexes and Constraints
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints?view=sql-server-2017

STATS_DATE (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/stats-date-transact-sql?view=sql-server-2017

sys.dm_db_stats_properties (Transact-SQL)

https://docs.microsoft.com/zh-tw/sql/relational-databases/system-dynamic-management-views/sys-dm-db-stats-properties-transact-sql?view=sql-server-2017