如何解决索引碎片？

碎片解决方案：
可以通过重新排列索引行和页面是物理和逻辑顺序相符来解决索引中的碎片问题，可以物理重排索引的叶子页面使其遵循索引的逻辑顺序：
 卸载并重建索引
 使用DROP_EXISTING子句重建索引
 在索引上执行ALTER INDEX REBUILD语句
 在索引上执行ALTER INDEX REORGANIZE语句

重建聚集/非聚集索引：
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD
ALTER INDEX AK_Department_Name ON HumanResources.Department REBUILD
DBCC DBREINDEX(table_name,index_name,fillfactor)

卸载并重建索引：
最大限度的减少了碎片，因为它使SQL Server完全为索引使用新的页面并且使用现有的数据恰当的填充了这些页面，同时避免了内部和外部碎片。但是有大量的缺点：
 阻塞：大量增加系统的开销，导致阻塞。卸载并重建索引阻塞表上（或表上的其他任何索引）的所有其他请求，也可能被表的其他请求阻塞
 丢失索引：因为索引被卸载并且可能被阻塞等待重建，对该表的查询没有可用的索引，导致性能低下，索引会被计划进行修补
 非聚集索引：如果被卸载的是一个聚集索引，表上的所有非聚集索引必须在聚集索引卸载之后被重建，然后聚集索引重建后又必须再次重建，导致进一步的阻塞和其他问题诸如存储过程重编译
 唯一性约束：定义主键或者唯一性约束的索引不能使用DROP INDEX语句删除，而且唯一性约束和主键都可能被外键约束引用，在主键卸载之前，所有引用该主键的外键必须首先被删除，是一种冒险而且费时的碎片整理方法。
基于以上原因，卸载并重建索引对于生产数据库只能在空闲时间使用。

使用DROP_EXISTING子句重建索引：
为了避免两次重建非聚集索引的开销，在重建聚集索引时，使用CREATE INDEX语句的DROP_EXISTING子句，在一个原子步骤中重建聚集索引，避免重建非聚集索引，因为行定位器使用的索引键值保持不变。
可以在聚集索引和非聚集索引中使用DROP_EXISTING子句，甚至将非聚集索引转换为聚集索引，但是不能将聚集索引转换为非聚集索引。缺点：
 阻塞：与卸载重建方法相似，也会导致来自其他访问该表（或该表的索引）的查询的阻塞问题
 使用约束的索引：和第一种方法不同，具有DROP_EXISTING子句的CREATE INDEX语句可以用于重建使用约束的索引，如果该约束是一个主键或与外键相关的唯一性约束，在CREATE语句中不能包含UNIQUE关键字
 具有多个碎片化的索引的表：随着表数据产生碎片，索引常常也碎片化。表上所有索引都必须单独确认和重建

执行ALTER INDEX REBUILD语句：
在一个原子步骤中重建索引，但允许索引（支持主键或唯一性约束）动态重建而不需要卸载并重建约束。
ALTER INDEX PROD_SERVICE_IDX3 ON dbo.PROD_SERVICE REBUILD --重建指定索引
ALTER INDEX ALL ON dbo.PROD_SERVICE REBUILD –-重建表的全部索引
缺点：
 阻塞：与前两种技术相似，它也阻塞其他尝试访问该表（或该表上的索引）的查询，也可能被这些查询阻塞
 事务回滚：是一个原子操作，如果在结束前停止，所有到那时为止进行的碎片整理操作都将丢失，可以使用ONLINE关键字运行，这将介绍锁机制，但是将增加重建索引所需的时间。

执行ALTER INDEX REORGANIZE语句：
减少索引的碎片而不需要重建索引，通过按照索引键的逻辑顺序重新排序列现有的索引叶子页面来减少外部索引。它压缩页面中的行，减少内部碎片，并且抛弃由此产生的空白页面，这种技术不使用任何新页面。
这种方式使用非原子联机方式，随着步骤进展，需要在短时间内请求少量的锁，步骤完成后，释放锁并继续下一步。如果尝试访问的一个页面正在被使用，将跳过该页面而不再返回。
如果该操作中途停止，所有执行过的碎片整理步骤保留，不回滚。
由于不使用任何新页面来重新排列索引并且跳过加锁的页面，因此去除碎片的数量通常少于ALTER INDEX REBUILD。
对于高度碎片化的索引，该方法可能比重建索引花费的时间更长。
如果索引跨越多个文件，不能够在文件之间移植页面。
最大的好处是：允许其他查询同时访问该表（或索引）。

去除碎片，可以有三种方法：删除并重新生成索引、原地重新生成索引、重新组织索引。
删除并重新生成索引最主要优势在于，几乎所有有关索引的东西都是可以改变的。
create unique clustered index PK_Address_AddressID on Person.Address(AddressID) with (fillfactor=90,drop_existing=on)
原地重新生成索引不能改变已存在的索引的列，也无法改变索引类型。
在默认情况下，重新生成索引是脱机（offline）操作。
重新生成聚集索引在整个操作期间都需要排他锁，意味着其他进程无法读写这个表。
重新生成非聚集索引需要共享锁，意味着对该表的写操作将被阻塞，而且操作期间索引不可用。
MSSQL SERVER2008企业版支持联机索引操作（online index operation），允许你联机创建、重新生成或删除索引，实际上就是SQL SERVER在幕后维护了两个索引，完成该操作后用新索引覆盖旧索引。联机索引操作要求数据库中有足够的空间，而且比脱机操作更慢。
alter index idx_cl_od on dbo.Orders rebuild with(fillfactor=90, online=on)

重新组织索引涉及一个冒泡排序算法，没有完全重新生成索引理想，并且要生成更多的日志，因此通常它会更慢，但消耗的系统资源少，不如重新生成索引来得彻底。只影响索引的叶级，并且总是联机执行。
alter index idx_cl_od on dbo.Orders reorganize

一般而言，索引的逻辑碎片<30%的时候用重新组织，超过用重新生成。

填充因子的重要性：
索引的内部碎片通过在索引中每个页面压缩更多的行来减少。
在一个叶子页面中压缩更多的行降低了索引所需要的总页面数，从而减少了检索一定范围的索引行所需要的磁盘I/O和逻辑读操作数量。
SQL Server允许使用填充因子（Fill Factor）控制索引叶子页面中的空闲空间数量。
如果表是只读的，可以创建一个高填充因子来减少索引页面的数量。
当页面分割时，通常原始页面的一半被移动到新的页面，这与索引创建中使用的填充因子无关。

使用一个填充因子重建聚集索引：
ALTER INDEX IX ON dbo.T1 REBUILD
WITH(FILLFACTOR=75)

ALTER INDEX REORGANIZE在索引创建期间考虑指定的填充因子；
ALTER INDEX REBUILD也考虑原始的填充因子，但是它允许在需要时指定一个新的填充因子。

对于默认和非默认的填充因子来说，索引（或表）的avg_page_space_used_in_percent最终都稳定在一个很窄的范围内，因此在大部分情况下，不需要手动维护填充因子，默认的一般已经足够好。只有当写操作达到读操作的较大比例（大于30%）时才使用填充因子选项。

自动维护：
在有大量事务的数据库中，表和索引随着时间的推移而碎片化。
维护步骤：
1) 确定当前数据库中所有需要分析碎片的用户表。
2) 确定所有用户表和索引的碎片。
3) 考虑以下因素以确定需要进行碎片整理的用户表和索引。
 高的碎片水平——avg_fragmentation_in_percent大于20%
 不是非常小的表/索引——也就是page_count大于8的
4) 整理具有大量碎片的表和索引