在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。
查询只返回了指定索引的碎片结果。