SQL Server 索引维护 索引碎片 填充因子

一、元数据函数sys.dm_db_index_physical_stats分析碎片

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'test');
SET @object_id = OBJECT_ID(N'dbo.TrackLog')
SELECT database_id,object_id,index_id,index_depth,avg_fragmentation_in_percent,page_count
FROM sys.dm_db_index_physical_stats(@db_id,@object_id,NULL,NULL,NULL);

下面看看统计信息的说明：

列名	数据类型	说明
database_id	smallint	表或视图的数据库 ID。
object_id	int	索引所在的表或视图的对象 ID。
index_id	int	索引的索引 ID。 0 = 堆。
partition_number	int	所属对象内从 1 开始的分区号；表、视图或索引。 1 = 未分区的索引或堆。
index_type_desc	nvarchar(60)	索引类型的说明： HEAP CLUSTERED INDEX NONCLUSTERED INDEX PRIMARY XML INDEX SPATIAL INDEX XML INDEX
alloc_unit_type_desc	nvarchar(60)	对分配单元类型的说明： IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA LOB_DATA 分配单元包含类型为text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 的列中所存储的数据。  ROW_OVERFLOW_DATA 分配单元包含类型为 varchar(n)、nvarchar(n)、varbinary(n) 和sql_variant 的列（已推送到行外）中所存储的数据。
index_depth	tinyint	索引总级别数。 1 = 堆，或 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。
index_level	tinyint	索引的当前位于B树结构中的级别。 0 表示索引叶级别、堆以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。 大于 0 的值表示非叶索引级别。 index_level 在索引的根级别中属于最高级别。 仅当 mode = DETAILED 时才处理非叶级别的索引。
avg_fragmentation_in_percent	float	索引的逻辑碎片，或 IN_ROW_DATA 分配单元中堆的区碎片。 此值按百分比计算，并将考虑多个文件。 0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 分配单元。 如果是堆表且mode模式 为 Sampled 时，为 NULL。如果碎片小于10%~20%，碎片不太可能会成为问题，如果索引碎片在20%~40%，碎片可能成为问题，但是可以通过索引重组来消除索引解决，大规模的碎片(当碎片大于40%)，可能要求索引重建。
fragment_count	bigint	IN_ROW_DATA 分配单元的叶级别中的碎片数。  对于索引的非叶级别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。 对于堆，当 mode 为 SAMPLED 时，为 NULL。
avg_fragment_size_in_pages	float	IN_ROW_DATA 分配单元的叶级别中的一个碎片的平均页数。 对于索引的非叶级别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。 对于堆，当 mode 为 SAMPLED 时，为 NULL。
page_count	bigint	索引或数据页的总数。 对于索引，表示 IN_ROW_DATA 分配单元中 b 树的当前级别中的索引页总数。 对于堆，表示 IN_ROW_DATA 分配单元中的数据页总数。 对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示该分配单元中的总页数。
avg_page_space_used_in_percent	float	所有页中使用的可用数据存储空间的平均百分比。 对于索引，平均百分比应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。 对于堆，表示 IN_ROW_DATA 分配单元中所有数据页的平均百分比。 对于 LOB_DATA 或 ROW_OVERFLOW DATA 分配单元，表示该分配单元中所有页的平均百分比。 当 mode 为 LIMITED 时，为 NULL。
record_count	bigint	总记录数。 对于索引，记录的总数应用于 IN_ROW_DATA 分配单元中 b 树(包括非叶子数据页的数量)的当前级别。 对于堆，表示 IN_ROW_DATA 分配单元中的总记录数。  注意 对于堆，此函数返回的记录数可能与通过对堆运行 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树结构的第几层。

 

 

 

关于碎片的解决方法

1.删除索引并重建

　　这种方式有如下缺点：

　　索引不可用：在删除索引期间，索引不可用。

　　阻塞：卸载并重建索引会阻塞表上所有的其他请求，也可能被其他请求所阻塞。

　　对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建，因为非聚集索引中有指向聚集索引的指针)。

　　唯一性约束：用于定义主键或者唯一性约束的索引不能使用DROP INDEX语句删除。而且，唯一性约束和主键都可能被外键约束引用。在主键卸载之前，所有引用该主键的外键必须首先被删除。尽管可以这么做，但这是一种冒险而且费时的碎片整理方法。

　　基于以上原因，不建议在生产数据库，尤其是非空闲时间不建议采用这种技术。

　　2.使用DROP_EXISTING语句重建索引

　　为了避免重建两次索引，使用DROP_EXISTING语句重建索引，因为这个语句是原子性的，不会导致非聚集索引重建两次，但同样的，这种方式也会造成阻塞。

CREATE UNIQUE CLUSTERED INDEX IX_C1 ON t1(c1)
WITH (DROP_EXISTING = ON)

　　缺点:

　　阻塞：与卸载重建方法类似，这种技术也导致并面临来自其他访问该表(或该表的索引)的查询的阻塞问题。

　　使用约束的索引：与卸载重建不同，具有DROP_EXISTING子句的CREATE INDEX语句可以用于重新创建使用约束的索引。如果该约束是一个主键或与外键相关的唯一性约束，在CREATE语句中不能包含UNIQUE。

　　具有多个碎片化的索引的表：随着表数据产生碎片，索引常常也碎片化。如果使用这种碎片整理技术，表上所有索引都必须单独确认和重建。

　　3.使用ALTER INDEX REBUILD语句重建索引

　　使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的，但依旧会造成阻塞。可以通过ONLINE关键字减少锁，但会造成重建时间加长。

　　阻塞：这个依然有阻塞问题。

　　事务回滚：ALTER INDEX REBUILD完全是一个原子操作，如果它在结束前停止，所有到那时为止进行的碎片整理操作都将丢失，可以通过ONLINE关键字减少锁，但会造成重建时间加长。

　　4.使用ALTER INDEX REORGANIZE

　　这种方式不会重建索引，也不会生成新的页，仅仅是整理叶级数据，不涉及非叶级，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种。

　　4种索引整理技术比较：

特性/问题	卸载并重建索引	DROP_EXISTING	ALTER INDEX REBUILD	ALTER INDEX REORGANIZE
在聚集索引碎片整理时，重建非聚集索引	两次	无	无	无
丢失索引	是	无	无	无
整理具有约束的索引的碎片	高度复杂	复杂性适中	简单	简单
同时进行多个索引的碎片整理	否	否	是	是
并发性	低	低	中等，取决于冰法用户活动	高
中途撤销	因为不使用事务，存在危险	进程丢失	进程丢失	进程被保留
碎片整理程度	高	高	高	中到低
应用新的填充因子	是	是	是	否
更新统计	是	是	是	否

 

 

设置如果插入更新操作较多的时候，可以通过填充因子的使用来减少分页

CREATE NONCLUSTERED INDEX INDEX_ROWDATE ON DBO.TRACKLOG(ROWDATE) with (FILLFACTOR=80)   

如何设置填充因子的值

---

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

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

    1.当读写比例大于100：1时，不要设置填充因子，100%填充

    2.当写的次数大于读的次数时，设置50%-70%填充

    3.当读写比例位于两者之间时80%-90%填充

    上面的数据仅仅是我的看法，具体设置的数据还要根据具体情况进行测试才能找到最优.