实际上，索引的维护主要包括以下两个方面：

• 　　页拆分
• 　　碎片

　　这两个问题都和页密度有关，虽然两者的表现形式在本质上有所区别，但是故障排除工具是一样的，因为处理是相同的。

　　对于非常小的表(比64KB小得多)，一个区中的页面可能属于多余一个的索引或表---这被称为混合区。如果数据库中有太多的小表，混合区帮助SQL Server节约磁盘空间。

　　随着表(或索引)增长并且请求超过8个页面，SQL Server创建专用于该表(或索引)的区并且从该区中分配页面。这样一个区被称为统一区，它可以为多达8个相同表或索引的页面请求服务。

　　新的页不断随数据的增长而产生，而聚集索引要求行之间连续，所以如果聚集索引不是自增列，页拆分后和原来的页在磁盘上并不连续-这就是外部碎片。
　　由于页拆分，导致数据在页之间的移动，所以如果插入更新等操作经常需要分页，则会大大消耗IO资源，造成性能下降。
　　对于查找连说，在有特定搜索条件，如where子句有很细的限制或者返回无序结果集时，外部碎片并不会对性能产生影响。但如果要返回扫描聚集索引而且查找连续页面时，外部碎片就会产生性能上的影响。所以当要读取相同的数连续的数据时需要扫描更多的页，更多的区。而且连续数据不能预读，造成额外的物理读，增加磁盘IO。通常，外部碎片过多会造成频繁的区切换。

　　如果页面连续排序，预读功能可以提前读取页面而不需要太多的磁头移动。

　　内部碎片：　　

　　内部碎片是页拆分后，导致索引页的数据并不满，有空行。同样读取一个索引页，却只能拿到x%的数据。

--新建一张表
CREATE TABLE Person
(
    Id    int,
    Name    char(999),
    Addr    varchar(10)
)

--聚集索引
CREATE CLUSTERED INDEX CIX ON Person(Id)

--插入8条数据
DECLARE    @var INT
SET @var=100
WHILE(@var < 900)
BEGIN
INSERT INTO Person(Id,Name,Addr)
VALUES(@var,‘xx‘,‘‘)
SET @var = @var+100
END

　　这个表每个行由int(4字节),char(999字节)和varchar(10字节组成），所以每行为1003个字节,则8行占用空间1003*8=8024字节加上一些内部开销，可以容纳在一个页面中。(原来这个表和数据搞得还挺巧的)。

　　执行查看语句：

　　SELECT page_count,avg_page_space_used_in_percent,record_count,avg_record_size_in_bytes,avg_fragmentation_in_percent,fragment_count 
　　FROM sys.dm_db_index_physical_stats
　　(DB_ID(‘Nx‘),object_id(‘dbo.Person‘),NULL,NULL,‘sampled‘)

　　示例如下：

　　

　　其中page_count是查看占用了多少个页，而第二个参数表示该页空间的使用率。因此从以上信息可以获得，这8条数据是放在一个页上，而且该页的空间使用率已经是百分之百了。

　　现在将其中一行的Addr改长一点：

　　UPDATE Person SET Addr = ‘广东广州‘ where Id = 100

　　则再执行检查索引语句：

　　

　　可以看到，这个表已经有了两页，页面平均使用为50%左右。但是明显也造成了碎片，在列avg_fragmentation_in_percent上可以看到，碎片大约为50%。

　　页拆分后的示意图如下：

　　

　　这个时候，继续插入数据，碎片会上升。在又插入了至达到48条记录后，碎片程度如下：

　　

　　这个时候，执行一个查询计划，查看下IO性能：

　　

　　可以看到I/O下降了不少。

 注意

对于堆，此函数返回的记录数可能与通过对堆运行 SELECT COUNT(*) 返回的行数不匹配。 这是因为一行可能包含多个记录。 例如，在某些更新情况下，单个堆行可能由于更新操作而包含一条前推记录和一条被前推记录。 此外，多数大型 LOB 行在 LOB_DATA 存储中拆分为多个记录。

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中总记录数。

当 mode 为 LIMITED 时，为 NULL。

ghost_record_count

bigint

分配单元中将被虚影清除任务删除的虚影记录数。

对于 IN_ROW_DATA 分配单元中索引的非叶级别，为 0。

当 mode 为 LIMITED 时，为 NULL。

version_ghost_record_count

bigint

由分配单元中未完成的快照隔离事务保留的虚影记录数。

对于 IN_ROW_DATA 分配单元中索引的非叶级别，为 0。

当 mode 为 LIMITED 时，为 NULL。

min_record_size_in_bytes

int

最小记录大小（字节）。

对于索引，最小记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。

对于堆，表示 IN_ROW_DATA 分配单元中的最小记录大小。

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的最小记录大小。

当 mode 为 LIMITED 时，为 NULL。

max_record_size_in_bytes

int

最大记录大小（字节）。

对于索引，最大记录的大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。

对于堆，表示 IN_ROW_DATA 分配单元中的最大记录大小。

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的最大记录大小。

当 mode 为 LIMITED 时，为 NULL。

avg_record_size_in_bytes

float

平均记录大小（字节）。

对于索引，平均记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。

对于堆，表示 IN_ROW_DATA 分配单元中的平均记录大小。

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的平均记录大小。

当 mode 为 LIMITED 时，为 NULL。

forwarded_record_count

bigint

堆中具有指向另一个数据位置的转向指针的记录数。 （在更新过程中，如果在原始位置存储新行的空间不足，将会出现此状态。）

除 IN_ROW_DATA 分配单元外，对于堆的其他所有分配单元都为 NULL。

当 mode = LIMITED 时，对于堆为 NULL。

compressed_page_count

bigint

压缩页的数目。

• 对于堆，新分配的页未进行 PAGE 压缩。 堆在以下两种特殊情况下进行 PAGE 压缩：大量导入数据时和重新生成堆时。 导致页分配的典型 DML 操作不会进行 PAGE 压缩。 当 compressed_page_count 值增长到超过您所需的阈值时，将重新生成堆。

• 对于具有聚集索引的表，compressed_page_count 值表示 PAGE 压缩的效率。

 　　通常返回多行的时候，有个index_level列，这个列表示改行属于B树结构的第几层。

　　分析小表的碎片

　　不要过分关注小表的sys.dm_db_index_physical_stats输出。对于少于8个页面的小表或者索引，SQL Server使用混合区。例如，如果一个表仅包含两个页面，SQL Server从一个混合区中分配两个页面，二不是分配一个区给该表。混合区也可以包含其他小表或索引的页面。

　　跨越多个混合区的页面分布可能导致你相信在表或索引中有大量的外部碎片，而实际上这是SQL Server的设计，因而是可接受的。

　　先来建一张表如下，3个int字段，1个char(2000)字段。平均尺寸为4+4+4+2000=2012字节，8KB的页面最多包含4行。在添加了28行之后，创建一个聚集索引来从屋里上排列行并将碎片减少到最低限度。

　　

　　咋一看，好像碎片非常厉害。实际上并不是这么回事。

　　分析如下：

• 　　avg_fragmentation_in_percent:尽管这个索引可能跨越多个区，这里看到碎片的情况并不是外部碎片的迹象，因为该索引保存在混合区上。
• 　　avg_page_space_used_in_percent:这说明所有或大部分县市在page_count中的7个页面中的数据存储状况良好。几乎满了，99点几。这消除了逻辑碎片的可能性。
• 　　fragment_count:这说明数据有碎片并且保存在多于一个区上，但是因为它的长度小于8个页面，SQL Server对存储该数据的地点没有很多选择。

　　尽管有上述引起误导的数值，一个少于8个页面的小表(或索引)不可能从去除碎片的工作中获益，因为它保存在混合区上。

　　索引说明：

　　

 　　填充因子的概念可以理解为预留一定的空间存放插入和更新新增加的数据，以避免页拆分：

　　

 　　可以看出，使用填充因子会减少更新或者插入时的分页次数，但由于需要更多的页，则会对应的损失查找性能.

 　　填充因子值的选择：

　　如何设置填充因子的值并没有一个公式或者理念可以准确的设置。使用填充因子虽然可以减少更新或者插入时的分页，但同时因为需要更多的页，所以降低了查询的性能和占用更多的磁盘空间.如何设置这个值进行trade-off需要根据具体的情况来看.

    具体情况要根据对于表的读写比例来看,我这里给出我认为比较合适的值:

1. 当读写比例大于100：1时，不要设置填充因子，100%填充
2. 当写的次数大于读的次数时，设置50%-70%填充
3. 当读写比例位于两者之间时80%-90%填充