如何将索引碎片数量降至最低

索引碎片能增大索引树的大小，增加不必要的IO，所以每隔一段时间对索引碎片进行检查时很有必要的。
下面一个示例一起来分析如何将索引降至最低。

新建一个表：

create   table  t3
(
i  int   primary   key ,
xx  varchar ( 200 )  not   null
)

加入数据：

declare   @x   int
set   @x   =   0
while   @x   < 1000
begin                        
insert   into  t3  values  ( @x , ' qweasdqweasdqweasdqweqweasdqwe ' )
set   @x   =   @x + 1
end

执行动态管理视图：

SELECT  index_id,index_type_desc,avg_fragmentation_in_percent,page_count  FROM  sys.dm_db_index_physical_stats( db_id (),  OBJECT_ID ( ' t3 ' ),  NULL ,  NULL  ,  ' LIMITED ' );

可以看到：


index_id为0表示这个是堆，平均的碎片有33%

现在执行几个可以减少碎片的方法都不管用，不能减少碎片。
包括：

DBCC  INDEXDEFRAG (test,  ' dbo.t3 ' , PK__t3__0EA330E9)

alter   index  PK__t3__0EA330E9  on  t3
rebuild

dbcc  dbreindex ( ' t3 ' )

这几个方法还有删除重建索引，都不能减少碎片数量。

后来我觉得是因为数据太少了，导致页也很少，数据库可能存在某种智能，判断是否值得去做重建索引的工作，所以加大的数据量：

declare   @x   int
set   @x   =   1000
while   @x   < 10000
begin                        
insert   into  t3  values  ( @x , ' qweasdqweasdqweasdqweqweasdqwe ' )
set   @x   =   @x + 1
end

再执行语句：

SELECT  index_id,index_type_desc,avg_fragmentation_in_percent,page_count  FROM  sys.dm_db_index_physical_stats( db_id (),  OBJECT_ID ( ' t3 ' ),  NULL ,  NULL  ,  ' LIMITED ' );

alter   index  t3index  on  t3
rebuild

显示出来了！


结论：
SQL Server在执行相关的操作的时候都会智能去判断是否值得去做，比如在页面数太小的情况下可以不去重建索引，rebuild reindex 。类似的，在SQL Server 2005 里面也多了许多智能的判断来保证一个完整庞大而又不失智能的设计，
比如：
生成查询计划的阀值
缓存机制，缓存的筛选，LRU算法
预读机制
checkpoint减少回滚距离
智能join判断
重编译

了解SQL Server这种类似的软件产品能够为我们在设计产品的时候提供更多的思路想法，即使你了解上面的东西对你的SQL开发也不会有太多帮助。

另外附上几种方式的区别：
reindex是比较好的选择，速度快，但是他不能在线操作
INDEXDEFRAG 比较慢，但是可以在线操作
rebuild建议在碎片较少时采用。

附上微软的重建索引脚本，从里面也可以看出微软根据碎片大小推荐的方式，不过这个要随每个不同的数据库而定。

--  ensure a USE <databasename> statement has been executed first.
SET  NOCOUNT  ON ;
DECLARE   @objectid   int ;
DECLARE   @indexid   int ;
DECLARE   @partitioncount   bigint ;
DECLARE   @schemaname  sysname;
DECLARE   @objectname  sysname;
DECLARE   @indexname  sysname;
DECLARE   @partitionnum   bigint ;
DECLARE   @partitions   bigint ;
DECLARE   @frag   float ;
DECLARE   @command   varchar ( 8000 );
--  ensure the temporary table does not exist
IF   EXISTS  ( SELECT  name  FROM  sys.objects  WHERE  name  =   ' work_to_do ' )
     DROP   TABLE  work_to_do;
--  conditionally select from the function, converting object and index IDs to names.
SELECT
     object_id   AS  objectid,
    index_id  AS  indexid,
    partition_number  AS  partitionnum,
    avg_fragmentation_in_percent  AS  frag
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
         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 );
     EXEC  ( @command );
     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 );
     EXEC  ( @command );
     END ;
PRINT   ' Executed  '   +   @command ;

FETCH   NEXT   FROM  partitions  INTO   @objectid ,  @indexid ,  @partitionnum ,  @frag ;
END ;
--  Close and deallocate the cursor.
CLOSE  partitions;
DEALLOCATE  partitions;

--  drop the temporary table
IF   EXISTS  ( SELECT  name  FROM  sys.objects  WHERE  name  =   ' work_to_do ' )
     DROP   TABLE  work_to_do;
GO

BOL的推荐：

avg_fragmentation_in_percent 值	修复语句
> 5% 且 < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

小于5没必要重建，所以上面的SQL语句还是有得商量的地方。

 

 

转载： http://www.cnblogs.com/perfectdesign/archive/2008/02/20/sqlserverreindexrebuild.html