讓 SQL Server 告訴你有哪些索引應該被重建或重組
我去年有一段時間睡覺前都在看 SQL Server 2005 證照的書(MCTS 70-431)，從中學到許多資料庫實際運作的技術細節，例如: 索引的結構。當資料庫中的索引碎裂(index fragmentation)程度過高時，索引的效率就會大大降低，為了避免這個問題發生，就必須定時替資料庫健檢(維護資料庫)，也就是進行索引重建(rebuild)或索引重組(reorganize)。
在 Microsoft SQL Server 2005 實作與維護 Ⅱ 此書的第12章有提到你可以透過 SELECT 指令搭配 sys.dm_db_index_physical_stats 這個動態管理函示(DMF, Dynamic Management Function) 可以查出資料庫中所有索引的碎裂狀態，如下 T-SQL 語法：
SELECT OBJECT_NAME(dt.object_id) ,
si.name ,
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
FROM
(SELECT object_id ,
index_id ,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE index_id <> 0
) AS dt --does not return information about heaps
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
執行後的結果長這樣：
但何時該做索引重建(ALTER INDEX…REBUILD)？又何時該做索引重組(ALTER INDEX…REORGANIZE)呢？此書也有提供一些建議值供資料庫管理員在進行索引維護時的參考。
索引重組的時機
- 檢查 External fragmentation 部分
- 當 avg_fragmentation_in_percent 的值介於 10 到 15 之間
- 檢查 Internal fragmentation 部分
- 當 avg_page_space_used_in_percent 的值介於 60 到 75 之間
索引重建的時機
- 檢查 External fragmentation 部分
- 當 avg_fragmentation_in_percent 的值大於 15
- 檢查 Internal fragmentation 部分
- 當 avg_page_space_used_in_percent 的值小於 60
由於索引的維護都是透過 ALTER INDEX 進行的，所以即便索引的數據分析出來後還是要人工下 ALTER INDEX 指令來重建或重組索引。最近看到一篇文章分享一個很實用的 T-SQL 指令，他可以自動幫你算出哪些索引需要被重建或重組，而且直接幫你把 ALTER INDEX 的 T-SQL 都寫好，程式碼如下：
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
CASE
WHEN ps.avg_fragmentation_in_percent > 15
THEN 'REBUILD'
ELSE 'REORGANIZE'
END +
CASE
WHEN pc.partition_count > 1
THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
ELSE ''
END,
avg_fragmentation_in_percent
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
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
WHERE ps.avg_fragmentation_in_percent > 10
AND ix.name IS NOT NULL
這段 T-SQL 只有檢查 External fragmentation 部分而已，而且上面分享的這段 T-SQL 語法與原文的數值不太一樣，我有調整過，讓判斷的臨界值符合 Microsoft SQL Server 2005 實作與維護 Ⅱ 書中建議的數值。以下是執行結果的圖示：
你只要複製這些語法，並且執行一遍，就可以完成索引維護了。
相關連結
- ALTER INDEX (Transact-SQL)
- sys.dm_db_index_physical_stats (Transact-SQL)
- Let SQL Server Tell You Which Indexes to Rebuild
- SQL SERVER - Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script
