MSSQL 檢查資料庫之資料表索引是否應該重建或重組
本文是學習筆記，內容來自於 [1] ，摘錄語法重點
目的
索引維護，改善效能
範例
檢查索引狀態
引用 [1] 之範例
SELECT OBJECT_NAME(dt.object_id) as TableName ,
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
FROMsys.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
根據 [1] 之說明，重建或重組之建議條件如下
索引重組的時機
-
檢查 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
重建索引
引用 [1] 之範例, 僅檢查 External Fragmentation
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
FROMsys.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
有需要重建的索引，其重建索引語法會被列出來，複製後執行即可完成索引之維護
