SQL Server Indexes Maintenance 索引維護
2021-12-02
筆記 SQL Server 如何查詢資料庫下的資料表需進行維護，自動產生出索引維護指令以及其目前的碎片化及資料列數，讓管理者判斷是否執行。
索引為何需要維護
索引維護需要維護的原因就是發生 fragmentation (碎片化)，而 fragmentation 的原因有二，分別是 External Fragmentation 以及 Internal Fragmentation。
External Fragmentation
因資料庫進行資料寫入、更新等異動時，資料頁的空間不足，為了加入新的資料會進行資料更新，於是資料分頁 (page split) 產生，造成邏輯順序與實際順序的不一致。
Internal Fragmentation
源自因資料寫入時，需要進行資料分頁 (page split)，而使資料頁存在過多的空白，造成資料頁數量膨脹，使查詢需要讀取更多的資料頁才能完成。
索引的碎片化程度，可以由 avg_fragmentation_in_percent 所觀察。
顯示資料庫的所有索引，包含名稱及碎片化程度 (avg_fragmentation_in_percent) 以及分頁數量 (page_count)
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count,
DDIPS.index_type_desc,
P.rows
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
INNER JOIN
sys.partitions P ON I.object_id = p.OBJECT_ID
AND I.index_id = p.index_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()索引維護
產生索引維護語法
進行 REBUILD 或者 REORGANIZE 的判斷在於 avg_fragmentation_in_percent，示範腳本是以 30 為分界，此外要大於 10 才會顯示 😋
SELECT ix.name,
'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
CASE
WHEN ps.avg_fragmentation_in_percent > 30
THEN 'REBUILD'
ELSE 'REORGANIZE'
END +
CASE
WHEN pc.partition_count > 1
THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
ELSE ''
END 'alter_script',
ps.avg_fragmentation_in_percent,
ps.page_count,
p.rows
FROM sys.indexes AS ix
INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN
(SELECT object_id,
index_id,
avg_fragmentation_in_percent,
partition_number,
page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
) ps
ON t.object_id = ps.object_id
AND ix.index_id = ps.index_id
INNER JOIN
(SELECT object_id,
index_id ,
COUNT(DISTINCT partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id,
index_id
) pc
ON t.object_id = pc.object_id
AND ix.index_id = pc.index_id
INNER JOIN
sys.partitions p ON ix.object_id = p.OBJECT_ID AND ix.index_id = p.index_id
WHERE ps.avg_fragmentation_in_percent > 10
AND ix.name IS NOT NULL
SQL Server Maintenance Solution
使用 IndexOptimize 進行索引維護，並細看各索引維護選項的用途。
sql-server-maintenance-solution | github
索引的維護策略
引用自 Index Maintenance Strategy | learn.microsoft：
💡 不要預期索引維護必定會帶來顯著的效能改善。
Do not assume that index maintenance will always noticeably improve your workload.
💡 衡量索引維護前後的具體效能改變，可以使用 Query Store 來輔助衡量。
Measure the specific impact of reorganizing or rebuilding indexes on query performance in your workload. Query Store is a good way to measure the "before maintenance" and "after maintenance" performance using the A/B testing technique.
💡 如果重建索引 (rebuild index) 對效能改善有幫助，可以評估用更新統計值 (updating statistics) 來替代，可能有相同的效果但較低的作業成本。
If you observe that rebuilding indexes improves performance, try replacing it with updating statistics. This may result in a similar improvement. In that case, you may not need to rebuild indexes as frequently, or at all, and instead can perform periodic statistics updates.
For some statistics, you may need to increase the sampling ratio using the WITH SAMPLE ... PERCENT or WITH FULLSCAN clauses (this is not common).
💡 藉由觀察索引碎片化程度 (index fragmentation)、頁面密度 (page density) 與查詢效能之間的關係，如果出現高碎片化或者低頁面密度影響效能，再進行索引的重組或重建，同時明確針對需要高碎片化以及低頁面密度的索引重建，勝於全面重建所耗費的資源及作業成本。
Monitor index fragmentation and page density over time to see if there is a correlation between these values trending up or down, and query performance. If higher fragmentation or lower page density degrade performance unacceptably, reorganize or rebuild indexes. It is often sufficient to only reorganize or rebuild specific indexes used by queries with degraded performance. This avoids a higher resource cost of maintaining every index in the database.
Establishing a correlation between fragmentation/page density and performance also lets you determine the frequency of index maintenance. Do not assume that maintenance must be performed on a fixed schedule. A better strategy is to monitor fragmentation and page density, and run index maintenance as needed before performance degrades unacceptably.
💡 經過綜合評量，重組或者重建索引是必須的作業下，慎選作業時段避免影響使用者，同時經常檢討索引維護的作業時段是否應該調整？
If you have determined that index maintenance is needed and its resource cost is acceptable, perform maintenance during low resource usage times, if any, keeping in mind that resource usage patterns may change over time.
參考資料
How to identify and resolve SQL Server Index Fragmentation
SQL Server Performance And Tuning
討論為什麼索引重建持續很久仍未完成，被 Block 的情況：