[程式]MS-Sql 2005 效能調校

來源： http://blog.miniasp.com/post/2009/01/Let-SQL-Server-Tell-You-Which-Indexes-to-Rebuild-or-Reorganize.aspx

　透過 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

索引重組的時機

• 檢查 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