SQL Server 2005 显示索引碎片

 

在SQL Server 2000中，使用DBCC SHOWCONTIG命令来显示索引碎片。碎片是表中数据修改自然产生的副产品。当在数据库中更新数据时，索引（基于索引键）的逻辑顺序不和实际数据页的物理顺序同步了。当数据页变得越来越无序，为了返回查询请求的结果，需要更多的I／O操作。重新生成或重新组织一个索引允许通过同步逻辑索引顺序、重排列物理数据页去匹配逻辑索引顺序，来整理索引的碎片。

注解  索引管理的介绍请看第5章，索引碎片整理和重新组织的介绍请看第23章。

现在在SQL Server 2005中，已经不建议使用DBCC SHOWCONTIG了，建议使用新的动态管理函数sys.dm_db_index_physical_stats。动态管理函数sys.dm_db_index_physical_stats返回能确定索引碎片级别的信息。

sys.dm_db_index_physical_stats的语法如下：

表28-5描述了这个命令的参数。

表28-5　sys.dm_db_index_physical_stats的参数

参　　数	描　　述
database_id | NULL	要检测索引的数据库ID。如果为NULL，返回SQL Server实例中的所有数据库的信息
object_id | NULL	要检测的表和视图（索引视图）的对象ID。如果为NULL，返回所有表的信息
index_id | NULL | 0	要检测的指定索引ID。如果为NULL，返回表中所有索引的信息
partition_number | NULL	要检测的分区的指定分区编号。如果为NULL，返回基于已定义数据库／表／选择的索引的所有分区的信息
LIMITED | SAMPLED | DETAILED | NULL | DEFAULT	这些模式影响了如何收集碎片数据。LIMITED模式扫描堆所有的页，但对于索引，则只扫描叶级上面的父级别页。SAMPLED收集在堆或索引中1%采样率的数据。DETAILED模式扫描所有页（堆或索引）。DETAILED是执行最慢的，但也是最精确的选项。指定NULL或DEFAULT的效果与LIMITED模式的相同

在这个示例中，查询动态管理视图sys.dm_db_index_physical_stats检索数据库AdventureWorks中平均碎片率大于30的所有对象：

它返回下面的（部分）结果：

第二个示例返回指定数据库、表和索引的碎片：

它返回

解析

第一个例子首先将数据库上下文改为数据库AdventureWorks：

由于OBJECT_NAME函数是数据库上下文敏感的，改变数据库上下文确保你查看到正确的对象。

下一步，SELECT子句显示对象名称、索引ID、描述和平均的碎片百分比：

列index_type_desc告诉你索引是堆、聚集索引、非聚集索引、主XML索引还是辅助XML索引。

下一步，FROM子句引用了sys.dm_db_index_physical_stats目录函数。括号中为参数，包括数据库名、扫描模式，其他参数为NULL：

由于sys.dm_db_index_physical_stats像表一样被引用（不像2000版本中的DBCC SHOWCONTIG），因此使用WHERE子句来限定只在结果中返回碎片百分比大于等于30%的行：

查询返回了数据库AdventureWorks中对象的碎片大于30%的几行。列avg_fragmentation_in_ percent显示聚集索引或非聚集索引的逻辑碎片，返回索引的叶级无序页的百分比。对于堆来说，avg_fragmentation_in_percent显示区级碎片。关于区，回忆一下SQL Server在页级读写数据。存储在块中的页被称为区，它由8个连续的8KB的页组成。使用avg_fragmentation_in_percent，你可以决定是否需要通过ALTER INDEX对索引重新生成或重新组织。

在第二个示例中，显示指定数据库、表和索引的碎片。SELECT子句包含了对索引名的引用（不是索引编号）：

FROM子句包含了指定的表名，它通过使用OBJECT_ID函数转换为ID。第三个参数包含了要检测碎片的索引的索引编号：

通过object_id和index_id，系统目录视图sys.indexes联结了函数sys.dm_db_index_physical_stats。

查询只返回了指定索引的碎片结果。