性能优化与故障排除百日谈(15)-索引的维护-找出索引碎片

索引已经是性能优化中大家常常提到而说到的问题，关于索引的很多的概念和解析，我们站点的索引进阶系列文章已经做了比较全面的讲述，我们这里就不在重复了。

我们都知道，对于索引而言，我们会面临两个问题：

1.是否建立了合适的索引

2.如何维护和诊断现有的索引

对于第一个问题，这确实不是三言两语可以讲清楚的，因为这首先需要对索引的知识掌握的非常清楚，而且还要知道建立索引的表中的数据的使用的情况（如读写的频率等）。我们会在之后的“收费阅读”版块，对索引进行深度的剖析，希望大家关注。

对于第二个问题，我们首先就要清楚：索引建立之后不是一劳永逸的，而是需要不断的维护，而且数据库中的数据是变化的，所以，此时的索引可以不适合或者需要进行一些处理，如重组等。

我们接下来的几篇文章会探讨下面的问题：

• 如何寻找索引碎片

• ·使用填充因子

• ·如何使用ReBuild来提高索引的效率

• ·如何使用ReOrganize来提高索引的效率

• ·如何找到缺失的索引

• 如何找到无用的索引

• ·如何找到高成本维护索引

• ·如何使用索引视图提升性能

• ·如何在计算列上面使用索引提升性能

 

 

碎片类型简述

内部碎片

说到碎片，这里的话题其实就很广了。我们这里主要讨论索引的碎片，至于碎片是如何产生的，我们这里暂不做过多的深究，大家可以参看这篇文章：http://www.agilesharp.com/showtopic-162.aspx。对于索引而言，碎片分为两种“外部碎片”和“内部碎片”，我这里用两个图简单的介绍一下：

 

 

大家可以看到：索引结构的页中，有很多的页中都是没有被填充的，这或许是我故意特定的，如在索引重建重组的时候我们可以指定页面的填充因子，但是很多的时候，这确实我们没有觉察到的。因为我们很多时候都以为索引页是被填满的，但是随着数据的增删改的进行，索引页中就发生了上述的内部碎片。

外部碎片

所谓的外部碎片，其实这就和索引结构的底层的物理存储相关了。我们上面看到的索引结构，其实就是索引的一个逻辑视图，因为实实在在的索引结构中的页的保存在物理存储上面是这样的：如图：

 

 

 

也就说，索引的数据会保存在一块存储空间里面，但是，这块存储空间同时也为其他的结构保存数据，如表，其他的索引等。所以，对一个某个索引，如A而言，那么，它所包含的页的存储的地址可能就不是连续的，如上图所示，里面标红的两个存储位置就是其他的对象的。但是，数据库在读取存储系统上面的数据的时候，是每次都会去读取连续的空间，而不是跳跃性的读取，如上面的，要读取A索引的全部数据，那么上面存储空间中两个标红的空间的数据也会被读取，这就是说：读取了我们原本不应该读取的数据。其实这一点，大家完全可以结合我们平时所知道的磁盘的碎片来理解。

 

 

下面我们就来看看如何来找碎片：

 

查找索引碎片

 

其实，在查找过程中，我们主要是依赖sys.dm_db_index_physical_stats这个动态管理函数。

 

内部碎片

 

下面，就给出查找内部碎片的查询，其实原理非常的简单，主要就是看页面空间的使用百分比，然后加上一些经验值的过滤添加，如下：

 

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
DECLARE @DefaultFillFactor INT 
DECLARE @Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)
INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)' 
SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 ELSE run_value END FROM @Fillfactor 

SELECT
DB_NAME() AS DBname,
QUOTENAME(s.name) AS CchemaName,
QUOTENAME(o.name) AS TableName,
i.name AS IndexName,
stats.Index_type_desc AS IndexType,
stats.page_count AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END AS [Fill Factor],
stats.avg_page_space_used_in_percent,
CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevel
FROM 
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'DETAILED') AS stats,
sys.objects AS o,
sys.schemas AS s,
sys.indexes AS iWHERE 
o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id AND i.OBJECT_ID = stats.OBJECT_ID AND i.index_id = stats.index_idAND stats.avg_page_space_used_in_percent<= 85 AND stats.page_count >= 10 AND stats.index_id > 0 ORDER BY stats.avg_page_space_used_in_percent ASC, stats.page_count DESC

 

我常常在Where中加入很多的过滤添加，因为之前说过，如果单看原始的数据，没有多大的意义。在上述的查询中，我们只对那些有至少10个页以上的、页面空间使用率小于85%的索引感兴趣。我们在上述查询中重点关注的字段就是：avg_page_space_used_in_percent和PageCount。

一般而言，导致avg_page_space_used_in_percent偏低的原因如下：

由于页面分割和删除记录：在这种情况下，我们必须重建或重组的索引。如果碎片在非叶级的，重建需要减少碎片。

由于填充因子设置：内部的碎片，这是因为填充因子设置的填充因子值的索引错误的设定可能会导致内部碎片，我们必须重建索引选择新的填充因子值。

由于记录大小：一些数据记录可能导致页发生分裂。例如，我们假设一个记录的大小是3000字节，那么一个索引页只能容纳两个记录。第三个记录不能被安装到一个页面，在页面中剩余的可用空间小于3000个字节。在这种情况下，每一页都将有2060个字节的空的空间。要摆脱的碎片的大小的记录，我们可能需要重新设计表或做一个垂直分区的表。

 

 

外部碎片

 

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
G
ODECLARE @DefaultFillFactor INT
DECLARE @Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)
INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)'
SELECT @DefaultFillFactor = CASE WHEN run_value=0 THEN 100 ELSE run_value END FROM @Fillfactor

SELECT
DB_NAME() AS DBname,
QUOTENAME(s.name) AS CchemaName,
QUOTENAME(o.name) AS TableName,
i.name AS IndexName,
stats.Index_type_desc AS IndexType,
stats.page_count AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END AS [Fill Factor],
stats.avg_fragmentation_in_percent,stats.fragment_count,
CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevel
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED') AS stats,
sys.objects AS o,
sys.schemas AS s,
sys.indexes AS iWHERE
o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id AND i.OBJECT_ID = stats.OBJECT_ID AND i.index_id = stats.index_idAND stats.avg_fragmentation_in_percent>= 20 AND stats.page_count >= 1000ORDER BY stats.avg_fragmentation_in_percent DESC,stats.page_count DESC

 

 

一般而言，导致avg_fragmentation_in_percent偏高的原因如下：
 

SQL Server存储引擎从混合区为一个表或索引分配页，直到的表或索引的数据大小达到8个页.一旦它们的页面数达到8页之后，存储引擎开始就开始为它们后续的数据存储分配统一的数据块（extent），然后把数据放在数据块的页中。如果数据库中有很多的小的数据表，那么它们的页将会被放在混合块中，加入，有某个数据表的页有7个，那么可能这7个页被分别放在不同的块中，也就说，这些页之间在存储上面可能是完全不连续的，这将会导致很大的碎片。
 

其他常见的原因是由于DML操作的页面拆分，而新分配的页和之前的页不在连续的存储空间中。

 

对于维护，给出以下建议：

• 碎片率在20%至40%，重新组织索引碎片

• 碎片率在40%以上，考虑重建索引

• 对于那些索引结构中，页数少于1000的，可以暂时不维护

• 如果有索引结构中页数超过5万，而且碎片率在10%和20%之间，也将被考虑重组。