一、碎片的产生
1. 内部碎片
SQL Server 是以页（8KB）为单位存储数据行和索引数据，因此索引行也不能跨页，也就导致索引页不能被完全填充。
在索引键偏大时，这种情况就比较明显。特别对于聚集索引而言，由于叶级索引页就是数据页，更容易导致内部碎片。例如，一张聚集索引的表，数据行固定为5KB，那么每页只能存放1行记录，相当于叶级索引页只有约60%的利用率。
2. 外部碎片
外部碎片指的是由于分页而产生的碎片。
向表中insert一个新行时（update语句在内部原理是利用一个delete语句后面紧跟一个insert语句来执行），SQL Server必须确定数据的插入位置，同时还要将相应的行插入到每个非聚集索引中。
当表是一个堆时，新行总是被插入到表中任意可用的空间。对于向有聚集索引的表中插入数据行和在非聚集索引中插入索引行，都必须根据新行在索引键列上的值来决定被插入的位置。这时候有3种可能：
（1）新行的位置被确定位于索引的最后。这时候，如果索引页的未尾还有空间，则直接插入新行；如果空间不足，则申请分配一个新页面并将该页面连接到B树上。
（2）新行必须插入到索引页的中间页面，并且该页面还有空间，则直接插入到该页面。
（3）新行必须插入到索引页的中间页面，但该页面已满，则发生分页，原始页面将被拆份，但又没有填满，从而产生外部碎片。
3. 拆分页
（1）拆分根页
如果拆分的是索引的根页，则会新分配2个页而作为索引上创建一个新的级别，根页则只有2行，分别指向新分配的页面，从而保证根页总是1页。
（2）拆分中间级页或叶级页
如果拆分的是索引的中间级页或叶级页，则原始页面的一半的行数被留在原始页面，另一半则被转移到新的页面上。SQL Server将尽可能使原始页与新页有差不多数量的行记录。
二、DBCC SHOWCONTIG 检查碎片程度
1. 语法
DBCC SHOWCONTIG
[ ( { table_name | table_id | view_name | view_id } [ , index_name | index_id ] ) ] [ WITH { [ , [ ALL_INDEXES ] ] [ , [ TABLERESULTS ] ] [ , [ FAST ] ] [ , [ ALL_LEVELS ] ] [ NO_INFOMSGS ] } ]
2. 示例
以前面的案例中，显示结果如下：
DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES
==== DBCC SHOWCONTIG 正在扫描 'person1' 表...表: 'person1' (245575913)；索引 ID: 1，数据库 ID: 8已执行 TABLE 级别的扫描。- 扫描页数................................: 4009- 扫描区数..............................: 502- 区切换次数..............................: 501- 每个区的平均页数........................: 8.0- 扫描密度 [最佳计数:实际计数].......: 100.00% [502:502]- 逻辑扫描碎片 ..................: 0.37%- 区扫描碎片 ..................: 0.80%- 每页的平均可用字节数.....................: 44.2- 平均页密度(满).....................: 99.45%DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 |
3. 结果集信息说明
（1）Extents（扫描区数）：
某个索引级别或整个堆中的区数。
（2）ExtentSwitches（区切换次数）：
遍历表或索引的页时，DBCC 语句从一个区移动到另一个区的次数。
（3）BestCount（最佳计数）：
所有内容连续时的区更改理想数量。
（4）ActualCount（实际计数）：
遍历表或索引的页时，区更改实际数量。
（5）ScanDensity（扫描密度）：
这是“最佳计数”与“实际计数”的百分比。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在一些碎片。
（6）LogicalFragmentation（逻辑扫描碎片）：
扫描索引的叶级页时返回的出错页的百分比。 此数与堆无关。 对于出错页，分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。实际上，逻辑碎片是在索引的叶级别中次序混乱页面的百分比。
（7）ExtentFragmentation（区扫描碎片）：
扫描索引的叶级页时出错区所占的百分比。 此数与堆无关。 对于出错区，包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。实际上，区碎片是在索引的叶级别中次序混乱区的百分比。
（8）AverageFreeBytes（每页的平均可用字节数）：
此数字越大，则页的填充程度越低。如果索引不会有很多随机插入，则数字越小越好。此数字还受行大小影响：行越大，此数字就越大。
（9）AveragePageDensity（平均页密度）：
页的平均密度，以百分比表示。该值会考虑行大小。因此，该值可以更准确地指示页的填充程度。百分比越大越好。
4. 判断碎片化
DBCC SHOWCONTIG 可确定表是否高度碎片化。索引的碎片程度可通过以下方式确定：
（1） 比较“区切换次数”和“扫描区数”的值
“区切换次数”的值应尽可能接近于“扫描区数”的值。 此比率将作为“扫描密度”值计算。 此值应尽可能的大，可通过减少索引碎片得到改善。
（2）了解“逻辑扫描碎片”和“区扫描碎片”的值
“逻辑扫描碎片”和“区扫描碎片”（对于较小的区）的值是表的碎片级别的最好指标。 这两个值应尽可能接近零，但 0% 到 10% 之间的值都是可接受的。
三. sys.dm_db_index_physical_stats 判断碎片程度
1. 兼容性
SQL Server 2012联机手册中有以下声明：后续版本的 Microsoft SQL Server 将删除DBCC SHOWCONTIG 功能。请不要在新的开发工作中使用该功能，并尽快修改当前还在使用该功能的应用程序。请改用 sys.dm_db_index_physical_stats。
2. 语法
sys.dm_db_index_physical_stats 函数需要5个参数，可以都使用默认值NULL，从而为当前SQL Server实例中每个数据库每个分区上每个表每个索引的每个级别返回21列数据。
重要的区别：DBCC SHOWCONTIG 是将共享锁 (S) 放置在包含索引的表上，而 sys.dm_db_index_physical_stats 仅放置一个意图共享锁 (IS)，从而在函数执行期间极大地减少了表的阻塞。
3. 碎片指标
（1）avg_fragmentation_in_percent
此列中所返回的值可确定索引的逻辑碎片（堆的区碎片）。逻辑碎片是在索引的叶级别中次序混乱页面的百分比，而区碎片是在索引的叶级别中次序混乱区的百分比。由于磁头只有左右跳动才能按照顺序读取页面，因此，逻辑碎片和区碎片会因为其需要额外的 I/O 和磁头运动而影响索引的性能。尽量保证逻辑碎片和区碎片均接近于零。
（2）avg_page_space_used_in_percent
此列可以确定索引页的填充度。为了正确配置该数字以使其尽量接近 100%，请在调整索引填充因子的同时观察所出现的页面分割数量。在某一刻，页面分割的数量会开始急剧增加，这表明您设置的索引填充因子数值高于其应有的数值。调整索引的填充因子需要花费一定的时间并需要进行测试，而且必须在事先进行合理规划。（如果未向索引中随意插入，则可以将索引填充因子设置为 100，且不必担心增加的页面分割数。）
四、GUI 查看索引属性
1. 查看索引的属性
2. 结果信息说明
（1）分区 ID
包含该索引的 B 树的分区 ID。
（2）建立虚影行版本
由于某个快照隔离事务未完成而保留的虚影记录的数目。
（3）平均行大小
叶级行的平均大小。
（4）前推记录数
堆中具有指向另一个数据位置的转向指针的记录数。在更新过程中，如果在原始位置存储新行的空间不足，将会出现此状态。
（5）深度
索引中的级别数（包括叶级别）。
（6）索引类型
索引的类型。可能的值包括 “聚集索引”、 “非聚集索引”和 “主 XML”。 表也可以存储为堆（不带索引），但此后将无法打开此“索引属性”页。
（7）虚影行数
标记为已删除，但尚未移除的行数。当服务器不忙时，将通过清除线程移除这些行。此值不包括由于某个快照隔离事务未完成而保留的行。
（8）叶级行数
叶级行的数目。
（9）页
数据页总数。
（10）最大行大小
叶级行最大大小。
（11）最小行大小
叶级行最小大小。
（12）页填充度
指示索引页的平均填充率（以百分比表示）。100% 表示索引页完全填充。50% 表示每个索引页平均填充一半。
（13）碎片总计
逻辑碎片百分比。用于指示索引中未按顺序存储的页数。
五、索引的重组与重建
在对表进行数据修改（INSERT、UPDATE 和 DELETE 语句）的过程中会出现索引的碎片现象，索引页的填满状态会随时间而改变。对于扫描部分或全部索引的查询，这样的索引碎片会导致读取额外的页。 从而延缓了数据的读取。
如果索引的碎片非常多，可选择以下方法来减少碎片：
1. 删除然后重新创建聚集索引
重新创建聚集索引将重新组织数据，从而使数据页填满。 填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。 这种方法的缺点是索引在删除/重新创建周期内为脱机状态，并且该操作是一个整体，不可中断。 如果中断索引创建，则不能重新创建索引。
DROP INDEX IX_person1_UserID ON person1 CREATE CLUSTERED INDEX IX_person1_UserID ON person1 (UserID) |
2. 对索引的叶级页按逻辑顺序重新排序
使用 INDEX…REORGANIZE，对索引的页级页按逻辑顺序重新排序。 由于此操作是联机操作，因此语句运行时索引可用。 此外，中断该操作不会丢失已完成的工作。 这种方法的缺点是在重新组织数据方面没有聚集索引的删除/重建操作有效。
REORGANIZE 操作通过对叶级页以物理方式重新排序，使之与叶节点的从左到右的逻辑顺序相匹配，进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。 重新组织还会压缩索引页。 压缩基于现有的填充因子值。
| ALTER INDEX IX_person2_UserID ON person2 REORGANIZE |
3. 重新生成索引
使用 REBUILD 和 ALTER INDEX 重新生成索引。此操作将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。 如果指定 ALL，将删除表中的所有索引，然后在单个事务中重新生成。
REBUILD 操作还有一个重要的选项：ONLINE=OFF 或者 ONLINE=ON。
| ALTER INDEX IX_person2_UserID ON person2 REBUILD WITH (ONLINE=OFF) |
online模式下，REBUILD 操作会复制旧索引来新建索引，此时旧的索引依然可以被读取和修改，但是所有在旧索引上的修改都会同步更新到新索引下。中间会有一些冲突解决机制。然后在REBUILD 即将完成的时候，会对table上锁一段时间，在这段时间里会用新索引来替换旧索引，当这个过程完成以后再释放table上面的锁。如果索引列包含 LOB对象的话，在SQL Server 2005等版本中rebuild index online会失败。在SQL server 2012中消除了这个限制，详见
offline模式下，REBUILD 会对table上锁，所有对这个table的读写操作都会被阻塞，在这期间新索引根据旧索引来创建，其实就是一个复制的过程，但是新索引没有碎片，最后使用新索引替换旧索引。当REBUILD 整个过程完成以后，table上面的锁才会被释放。
六、填充因子
1. 填充因子
在向索引中添加新行时容易发生分页，不仅在页拆分时会降低性能，还会导致产生过多的索引碎片（内部碎片）。
SQL Server允许在创建索引时指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，减少页拆分的可能性。
填充因子的值是从 0 到 100 之间的百分比数值，指定在创建索引后对数据页的填充比例。值为 0或100 时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会频繁发生更改时，这种设置更为适当。
2. 通过脚本修改填充因子并重建索引
| ALTER INDEX IX_person2_UserID ON person2 REBUILD WITH (FILLFACTOR = 60) |
3. 通过GUI修改填充因子