哪些索引應該被重建或重組?

ct.soft

可以透過 SELECT 指令搭配 sys.dm_db_index_physical_stats 這個動態管理函示(DMF, Dynamic Management Function) 可以查出資料庫中所有索引的碎裂狀態，如下 T-SQL 語法：

SELECT OBJECT_NAME(stat.object_id)      ,
       sind.name                        ,
       stat.avg_fragmentation_in_percent,
       stat.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 stat
       INNER JOIN sys.indexes sind
       ON     sind.object_id = stat.object_id
          AND sind.index_id  = stat.index_id


但何時該做索引重建(ALTER INDEX…REBUILD)？又何時該做索引重組(ALTER INDEX…REORGANIZE)?
參考 Microsoft SQL Server 2005 實作與維護 Ⅱ 文章建議

<索引重組的時機>
檢查 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 指令來重建或重組索引。
參自 http://weblogs.asp.net/okloeten/archive/2009/01/05/6819737.aspx 並依上述建議值修改
可以自動幫你算出哪些索引需要被重建或重組，且直接把 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