在索引碎片里我们解释了不同类型的碎片，还有它们如何影响查询性能。在这个文章里，我们会讨论下如何检测索引碎片。

内部碎片检测

内部碎片是关于页面饱和度的一切，可以用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

原标题：索引碎片检测

关键词：