筆記 SQL Server 如何查詢資料庫下的資料表需進行維護,自動產生出索引維護指令以及其目前的碎片化及資料列數,讓管理者判斷是否執行。
索引為何需要維護
索引維護需要維護的原因就是發生 fragmentation (碎片化),而 fragmentation 的原因有二,分別是 External Fragmentation 以及 Internal Fragmentation。
External Fragmentation
因資料庫進行資料寫入、更新等異動時,資料頁的空間不足,為了加入新的資料會進行資料更新,於是資料分頁 (page split) 產生,造成邏輯順序與實際順序的不一致。
Internal Fragmentation
源自因資料寫入時,需要進行資料分頁 (page split),而使資料頁存在過多的空白,造成資料頁數量膨脹,使查詢需要讀取更多的資料頁才能完成。
索引的碎片化程度,可以由 avg_fragmentation_in_percent 所觀察。
索引維護
進行 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
參考資料
How to identify and resolve SQL Server Index Fragmentation
SQL Server Performance And Tuning