索引碎片

　　碎片在表中的数据被修改时产生。当插入或者更新表中的数据时，表的对应聚簇索引和受到影响的聚簇索引被修改。如果对索引的修改不能容纳于同一页面中，可能导致索引叶子页面分割。一个新的叶子页面将被添加以包含原来的部分，并且维持索引键中行的逻辑顺序。虽然新的叶子页面维护原始页面中行的逻辑顺序，但是这个新的页面通常在磁盘上与原来页面相邻。或者，索引逻辑关键字顺序与文件中的物理顺序不相同。

　　页面被集合成更大的单元---区。Sql server使用一个区作为磁盘上的物理分配单元。在理想状态下，包含一个索引的叶子页面的区的物理顺序应该和索引的逻辑顺序相同。这减少了检索一定范围的索引行时所需要的区之间的切换次数。但是，页面分割可能物理上打乱区中的页面顺序，也可能从物理上打乱区本身的顺序。

　　有了分布在两区的叶子页面，理想状态下，预期使用最多一次两区之间切换就可以读取一定范围的索引行。但是，区之间无组织的页面可能导致在读取一定范围的行数时超过一次的区切换。这种碎片被称为外部碎片。

　　数据操作引起的页面分割已经在第一个叶子页面中创建了一个空位，这被称为内部碎片。对于高事务性的数据库，特意在叶子页面中留下一些空闲空间很受欢迎的，这样添加新行，或者修改现有行的大小，而不造成页面分割。

　　内部和外部碎片对数据检索性能都有负面影响。外部碎片导致磁盘上的索引页面不连续，新的叶子页面和原始叶子页面离的很远，物理顺序与逻辑顺序不同。因此，在索引上的一个范围扫描将需要比理想状态下更多的对应区之间的切换。内部碎片的情况下，行被稀疏的分散到大量的页面中，增加了读取索引页面到内存中所需的磁盘I/O操作，并且增加了从内存检索多个索引所需的逻辑读数量，即使增加了数据检索的开销，少数的内部碎片仍有可能有利，因为它使你在操作的时候不会引起页面分割，对于不必要穿越一系列页面检索数据的查询，碎片可能没有太大的影响。

解决碎片的方案：

可以通过重新排列索引行和页面使物理和逻辑顺序相符来解决索引碎片的问题，为了减少外部碎片，可以物理重排索引的叶子页面使其遵循索引的逻辑顺序。

1. 卸载并重建索引
2. 使用Drop_existing子句重建索引
3. 在索引上执行ALTER INDEX REBULID语句
4. 在索引上执行ALTER INDEX REORGANIZE语句

索引碎片整理脚本：

set nocount on

declare @objectid         int
       ,@indexid          int
       ,@partitioncount   bigint
       ,@schemaname       sysname
       ,@objectname       sysname
       ,@indexname        sysname
       ,@partitionnum     bigint
       ,@partitions       bigint
       ,@frag             float
       ,@command          varchar(1000)

select 
objectid     = object_id
      ,indexid      = index_id
      ,partitionnum = partition_number
      ,frag         = avg_fragmentation_in_percent
  into #work_to_do
  from sys.dm_db_index_physical_stats(db_id(), null, null , null, 'LIMITED')
where avg_fragmentation_in_percent > 10.0
   and index_id > 0

-- declare the cursor for the list of partitions to be processed.
declare partitions cursor for
   select * from #work_to_do

-- Open the cursor.
open partitions

-- Loop through the partitions.
fetch next from partitions into @objectid, @indexid, @partitionnum, @frag

while @@fetch_status = 0 begin
   select @objectname = o.name, @schemaname = s.name
     from sys.objects as o
            inner join sys.schemas as s
      on s.schema_id = o.schema_id
   where o.object_id = @objectid

   select @indexname = name
     from sys.indexes
    where object_id = @objectid
      and index_id = @indexid

   select @partitioncount = count (*)
    from sys.partitions
   where object_id = @objectid
     and index_id = @indexid

   -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
   if @frag < 30.0 begin
       select @command = 'alter index ' + @indexname + ' on ' 
                       + @schemaname + '.' + @objectname + ' reorganize'

       if @partitioncount > 1
           select @command = @command + ' partition=' + convert(char, @partitionnum)
   end

   if @frag >= 30.0 begin
       select @command = 'alter index ' + @indexname +' on ' 
                       + @schemaname + '.' + @objectname + ' rebuild'

       if @partitioncount > 1
           select @command = @command + ' partition=' + convert(char, @partitionnum)
   end

   -- exec (@command)
   print 'Executed: ' + @command

   fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
end

-- free resource
close partitions
deallocate partitions
drop table #work_to_do