FW:SQL SERVER index 何時應該重建

索引重建的時機

 

1.檢查 External fragmentation 部分

  當 avg_fragmentation_in_percent 的值大於 15

2.檢查 Internal fragmentation 部分

  當 avg_page_space_used_in_percent 的值小於 60

 

他可以自動幫你算出哪些索引需要被重建或重組，而且直接幫你把 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

 

ref:

https://blog.miniasp.com/post/2009/01/18/Let-SQL-Server-Tell-You-Which-Indexes-to-Rebuild-or-Reorganize.aspx