SqlServer2005 性能调校之利用sys.dm_db_index_physical_stats查看索引大小/碎片-CSDN博客

我们都知道，提高sql server的数据查询速度，最有效的方法，就是为表创建索引，而我们对数据表进行新增，删除，修改的时候，会产生索引碎片，索引碎片多了，对性能产生很大的影响，索引碎片越多对数据库查询的性能影响就越大，这时我们就需要对索引重新组织或重新生成，以达到索引的最大效率。

在sqlserver中索引碎片的大小是以百分比来体现的，如果索引碎片的百分比大于30%以上，就会对影响查询的性能。这时候我们就需要对该索引进行重新组织或重新生成了，重新组织或重新生成之后，索引碎片就会被删除。

那我们怎么才能知道每张表索引碎片的大小呢？方法有两种：
第一种：

1.右键索引，选择“全部重新组织”，会显示当前表全部所有索引，这是就可以看到“碎片总计”值是以百分比体现的，这时如果值有超过30%的，就要考虑是否要重新组织了。

2.如果要重新组织的话，点击“确定”即可。

第二种：利用 sys.dm_db_index_physical_stats 动态函数来查看索引的碎片比率

sys.dm_db_index_physical_stats的定义：

sys.dm_db_index_physical_stats ( 
    {database_id | NULL }
    , { object_id | NULL }
    , { index_id | NULL | 0 }
    , { partition_number | NULL }
    , { mode | NULL | DEFAULT }
)

参数解释：

database_id--要查看索引所在数据库，当前数据库ID我们可以用db_id()函数来取到
object_id--要查索引所在表的id,比如我们要查看表T1，可以用object_id('T1')来取到该表的ID
index_id--要查看索引的索引号,该索引也同样可以用object_id('索引名')来获取
partition_number--对象中的分区号。partition_number为int类型。有效的输入包括索引或堆的 partion_number 或 NULL
mode--
函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。mode 被指定为 LIMITED、SAMPLED 或 DETAILED。此函数将遍历构成表或索引的指定分区的分配单元页链。
LIMITED：模式运行最快，扫描的页数最少。对于堆，它将扫描所有页，但对于索引，则只扫描叶级上面的父级别页。

SAMPLED：模式将返回基于索引或堆中所有页的 1％样本的统计信息。如果索引或堆少于 10,000 页，则使用DETAILED模式代替 SAMPLED。

DETAILED：模式将扫描所有页并返回所有统计信息。

从 LIMITED 到 DETAILED 模式，速度将越来越慢，因为在每个模式中执行的任务越来越多。若要快速测量表或索引的大小或碎片级别，请使用 LIMITED 模式。它的速度最快，并且对于索引的IN_ROW_DATA 分配单元中的每个非叶级别，不返回与其对应的一行。

下面通过示例来查看：
比如我要查看当前数据库的dbo.Orders表的索引情况，可以用以下的语句查询:

SELECT * FROM sys.dm_db_index_physical_stats(db_id(),object_id('dbo.Orders'),null,null,null)

有用的列名解释：

database_id：当前数据库ID，也就是db_id()
object_id列：当前表的ID,也就是object_id('dbo.Orders')
index_id列:当前索引的ID，假如dbo.Orders中有两个索引，index_id分别为1和2,如果我们只想查看索引1的情况，可以用
select * from sys.dm_db_index_physical_stats(db_id(),object_id('dbo.Orders'),1,null,null)
partition_number列：当前索引所在分区号，同sys.dm_db_index_physical_stats定义中的partition_number参数
index_type_desc列：索引类型-聚集索引或者非聚集索引等。
alloc_unit_type_desc列：分配单元类型--这个在下面解释
avg_fragmentation_in_percent列：最重要的列，当前索引碎片比率。

分配单元类型讲解：
IN_ROW_DATA：包含除大型对象 (LOB) 数据以外的所有数据的数据行或索引行。页的类型为 Data 或 Index。

LOB_DATA：以下列一种或多种数据类型存储的大型对象数据：text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max) 或 CLR 用户定义类型 (CLR UDT)。页的类型为 Text/Image。

ROW_OVERFLOW_DATA：存储在超过 8,060 字节行大小限制的 varchar、nvarchar、varbinary 或 sql_variant 列中的可变长度数据。页的类型为 Data。

注意：有时候，我们只为表创建了一个索引，但通过sys.dm_db_index_physical_stats却查出了两条记录，这就是因为在表中使用了text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)和xml列，也就是LOB_DATA存储单元.使用了这些列后，在创建表但未插入数据前只有一条记录，但有插入过数据后，就会有两条记录了，两条记的区别就是alloc_unit_type_desc中的值的区别，可能的情况就是一条的值是N_ROW_DATA，另一条的值是LOB_DATA。
我们可以通过一个例子来测试：
首先，新建一个表并且建立一个聚合索引：

CREATE  TABLE  TestTable(rows NCHAR (200) ,rows2 NVARCHAR (max) )
CREATE  CLUSTERED  INDEX  _data_index_TestTable ON  TestTable(rows)

在没有插入数据的情况下：

SELECT * FROM TestTable
SELECT  * FROM  sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TestTable'),null,null,null)

如下图，在创建表但未插入数据前只有一条记录：

然后，插入数据的情况下：

INSERT  INTO  TestTable SELECT  REPLICATE('li',50),REPLICATE('hongkong_8',2000)
SELECT * FROM TestTable
SELECT  * FROM  sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID ('TestTable'),null,null,null)如下图，