在索引碎片里我們解釋了不同類型的碎片，還有它們如何影響查詢性能。在這個文章里，我們會討論下如何檢測索引碎片。

內部碎片是關于頁面飽和度的一切，可以用DETAILED模式的sys.dm_db_index_physical_stats，avg_page_space_used_in_percent列會給出索引的內部碎片，下面的查詢會列出超過10個頁面，且頁面飽和度低于85%的索引。

 1 EXEC sp_configure 'show advanced options', 1 2 GO 3 RECONFIGURE WITH OVERRIDE 4 GO 5 DECLARE @DefaultFillFactor INT  6 DECLARE @Fillfactor TABLE 7     ( 8       Name VARCHAR(100) , 9       Minimum INT ,10       Maximum INT ,11       config_value INT ,12       run_value INT13     )14 INSERT  INTO @Fillfactor15         EXEC sp_configure 'fill factor (%)'     16 SELECT  @DefaultFillFactor = CASE WHEN run_value = 0 THEN 10017                                   ELSE run_value18                              END19 FROM    @Fillfactor 20 21 SELECT  DB_NAME() AS DBname ,22         QUOTENAME(s.name) AS CchemaName ,23         QUOTENAME(o.name) AS TableName ,24         i.name AS IndexName ,25         stats.Index_type_desc AS IndexType ,26         stats.page_count AS [PageCount] ,27         stats.partition_number AS PartitionNumber ,28         CASE WHEN i.fill_factor > 0 THEN i.fill_factor29              ELSE @DefaultFillFactor30         END AS [Fill Factor] ,31         stats.avg_page_space_used_in_percent ,32         CASE WHEN stats.index_level = 0 THEN 'Leaf Level'33              ELSE 'Nonleaf Level'34         END AS IndexLevel35 FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')36         AS stats ,37         sys.objects AS o ,38         sys.schemas AS s ,39         sys.indexes AS i40 WHERE   o.OBJECT_ID = stats.OBJECT_ID41         AND s.schema_id = o.schema_id42         AND i.OBJECT_ID = stats.OBJECT_ID43         AND i.index_id = stats.index_id44         AND stats.avg_page_space_used_in_percent <= 8545         AND stats.page_count >= 1046         AND stats.index_id > 047 ORDER BY stats.avg_page_space_used_in_percent ASC ,48         stats.page_count DESC

這里我在WHERE條件里指定了只列出超過10頁，且頁面飽和度低于85%的結果。這是基于我當前系統環境和一些文檔的最佳實踐。avg_page_space_used_in_percent的低值，加上PageCount的高值，會影響到系統性能。avg_page_space_used_in_percent的低值會下列不同的原因：

• 分頁和記錄刪除：在這個情況下，我們需要對索引進行REBUILD和REORGANIZE操作。如果碎片報告在非頁層，需要REBUILD來減少碎片。
• 填充因子設置：索引填充因子值的錯誤設置可能造成內部碎片。如果內部碎片是因為填充因子設置造成的，我們需要使用填充因子的新值對索引進行REBUILD。
• 記錄大小：有些時候，記錄大小也會稱為內部碎片的原因。例如我們假設一條記錄大小是3000 bytes，一頁只能保存2條記錄。第3條記錄不能插入頁，因為頁里剩下的空間小于3000 bytes。在這種情況下，每一頁都會有2060 bytes的空余。為了解決因這個原因造成的碎片，我們需要重新設計表或者對表進行垂直分區。

外部檢測也是用LIMITED模式的sys.dm_db_index_physical_stats，但我們使用avg_fragmentation_in_percent的結果來檢測外部碎片。使用LIMITED模式會給我們葉子層的碎片。如果要獲得非頁層的碎片，可以使用DETAILED或SAMPLE模式。碎片是頁的連續分配。例如如果一個索引有150頁，頁分配從1到50，55到60，65到120，還有140到180。每個這樣序列被稱為碎片，這里就是有4個碎片。

 1 EXEC sp_configure 'show advanced options', 1 2 GO 3 RECONFIGURE WITH OVERRIDE 4 GO 5 DECLARE @DefaultFillFactor INT  6 DECLARE @Fillfactor TABLE 7     ( 8       Name VARCHAR(100) , 9       Minimum INT ,10       Maximum INT ,11       config_value INT ,12       run_value INT13     )14 INSERT  INTO @Fillfactor15         EXEC sp_configure 'fill factor (%)'     16 SELECT  @DefaultFillFactor = CASE WHEN run_value = 0 THEN 10017                                   ELSE run_value18                              END19 FROM    @Fillfactor 20 21 SELECT  DB_NAME() AS DBname ,22         QUOTENAME(s.name) AS CchemaName ,23         QUOTENAME(o.name) AS TableName ,24         i.name AS IndexName ,25         stats.Index_type_desc AS IndexType ,26         stats.page_count AS [PageCount] ,27         stats.partition_number AS PartitionNumber ,28         CASE WHEN i.fill_factor > 0 THEN i.fill_factor29              ELSE @DefaultFillFactor30         END AS [Fill Factor] ,31         stats.avg_fragmentation_in_percent ,32         stats.fragment_count ,33         CASE WHEN stats.index_level = 0 THEN 'Leaf Level'34              ELSE 'Nonleaf Level'35         END AS IndexLevel36 FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')37         AS stats ,38         sys.objects AS o ,39         sys.schemas AS s ,40         sys.indexes AS i41 WHERE   o.OBJECT_ID = stats.OBJECT_ID42         AND s.schema_id = o.schema_id43         AND i.OBJECT_ID = stats.OBJECT_ID44         AND i.index_id = stats.index_id45         AND stats.avg_fragmentation_in_percent >= 2046         AND stats.page_count >= 100047 ORDER BY stats.avg_fragmentation_in_percent DESC ,48         stats.page_count DESC

在這個查詢里，我使用的WHERE條件只列出碎片大于20%且最少1000頁的索引。avg_fragmentation_in_percent值高的話，可能有下列原因：

• SQL Server存儲引擎對表或索引從混合區開始分配頁，直到頁數達到8頁。一旦頁數達到8頁，SQL Server引擎開始把整個統一區分配給索引。因此這里對于小表會有很高的碎片，重建索引會增加碎片。例如，我們假設一個索引有7頁，這些頁是從2個混合區分配的，當我們重建索引的時候，很可能把頁分配從2個混合區變成最大7個混合區，這就導致了碎片增加。
• 即使也從混合區分配，還是有碎片產生的可能。當索引大小增長時，在非頁層也需要更多的頁。如果分配給葉子層的最后頁是250，在葉子層索引結構里增加更多的記錄，可能會在第1層索引需要更多的頁，然后SQL Server存儲引擎分配251頁在第1層索引，這就在葉子層產生了碎片。
• 造成分頁的其他常見原因就是DML操作。Rebuild/Reorganize索引對于上述不能很好的減少碎片，但可以減少由分頁或刪除操作造成的碎片。
• 我們按下列要求進行索引的維護：
  • 20-40%的碎片，用Reorganize來重新組織索引。
  • 大于40%的碎片，需要用Rebuild來重建索引。
  • 低于1000頁的索引，在索引維護邏輯上是被忽略的（不處理）
  • 大于50k的頁，碎片在10-20%之間，也要用Reorganize來重新組織索引。