# 讓 SQL Server 告訴你有哪些索引應該被重建或重組

我去年有一段時間睡覺前都在看 SQL Server 2005 證照的書([MCTS 70-431](https://web.archive.org/web/20090505115047/http://www.microsoft.com/learning/en/us/exams/70-431.aspx))，從中學到許多資料庫實際運作的技術細節，例如: 索引的結構。當資料庫中的索引碎裂(index fragmentation)程度過高時，索引的效率就會大大降低，為了避免這個問題發生，就必須定時替資料庫健檢(維護資料庫)，也就是進行索引重建(rebuild)或索引重組(reorganize)。

在 [Microsoft SQL Server 2005 實作與維護 Ⅱ](https://web.archive.org/web/20170206195557/http://www.delightpress.com.tw/book.aspx?book_id=SKTD00002) 此書的第12章有提到你可以透過 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
```

執行後的結果長這樣：

[![查出資料庫中所有索引的碎裂狀態](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/1cbbb77363b816371f36-image_thumb.png)](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/982a9c6f5b60e5c1b4aa-image_2.png)

但何時該做索引重建(ALTER INDEX…REBUILD)？又何時該做索引重組(ALTER INDEX…REORGANIZE)呢？[此書](https://web.archive.org/web/20170206195557/http://www.delightpress.com.tw/book.aspx?book_id=SKTD00002)也有提供一些建議值供資料庫管理員在進行索引維護時的參考。

**索引重組的時機**

-   檢查 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](http://msdn.microsoft.com/zh-tw/library/ms188388.aspx) 進行的，所以即便索引的數據分析出來後還是要人工下 [ALTER INDEX](http://msdn.microsoft.com/zh-tw/library/ms188388.aspx) 指令來重建或重組索引。最近看到一篇[文章](https://web.archive.org/web/20131004103834/http://weblogs.asp.net/okloeten/archive/2009/01/05/6819737.aspx)分享一個很實用的 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
```

這段 T-SQL 只有檢查 External fragmentation 部分而已，而且上面分享的這段 T-SQL 語法與[原文](https://web.archive.org/web/20131004103834/http://weblogs.asp.net/okloeten/archive/2009/01/05/6819737.aspx)的數值不太一樣，我有調整過，讓判斷的臨界值符合 [Microsoft SQL Server 2005 實作與維護 Ⅱ](https://web.archive.org/web/20170206195557/http://www.delightpress.com.tw/book.aspx?book_id=SKTD00002) 書中建議的數值。以下是執行結果的圖示：

![自動幫你算出哪些索引需要被重建或重組的結果](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/070c3bc05a00abc08340-image_5.png)

你只要複製這些語法，並且執行一遍，就可以完成索引維護了。

**相關連結**

-   [ALTER INDEX (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms188388.aspx)
-   [sys.dm\_db\_index\_physical\_stats (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms188917.aspx)
-   [Let SQL Server Tell You Which Indexes to Rebuild](https://web.archive.org/web/20131004103834/http://weblogs.asp.net/okloeten/archive/2009/01/05/6819737.aspx)
-   [SQL SERVER - Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script](http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/)