SQL SERVER整理索引碎片测试

引用地址：

http://blog.chinaunix.net/u2/79339/showart_1308678.html

 

SQL SERVER整理索引碎片测试

SQL SERVER整理索引的方法也就这么几种，而且老是自作聪明的加入智能判断很不爽，还是比DBMS_ADVISOR差远了：

1SQL SERVER 2000/2005

查询

2000 use DB_Name declare @table_id int set @table_id=object_id('Table_Name') dbcc showcontig(@table_id) 2005 SELECT index_id,index_type_desc,avg_fragmentation_in_percent,page_count FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('Table_Name'), NULL, NULL , 'LIMITED');

整理

2000 DBCC INDEXDEFRAG (DB_Name, 'dbo.Table_Name', Index_Name) DBCC DBREINDEX ('DB_Name.dbo.Table_Name','',100) 2005 ALTER INDEX Index_Name ON dbo.Table_Name REBUILD

以下是一个网上找的不错的例子，链接是：
http://www.cnblogs.com/perfectdesign/archive/2008/02/20/sqlserverreindexrebuild.html
新建一个表：

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://blog.youkuaiyun.com/greenery/archive/2008/08/06/2778486.aspx

/************************************** 作用:在线整理索引碎片 时间:2008-5-15 说明：           根据微软的示例改编           适用于无法停机重建索引的情况，           在线整理索引碎片，并更新表的统计信息以增强效果。           另外可以自定义需要整理的索引的逻辑碎片程度，扫描密度，页数           必须要在数据库访问较少的时候执行 ***************************************/ CREATE PROC [dbo].[SYS_IndexDrag] AS BEGIN SET NOCOUNT ON DECLARE @tablename VARCHAR (128) DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @maxfrag DECIMAL--逻辑碎片 DECLARE @MaxScanDensity DECIMAL--扫描密度 DECLARE @Page INT --8k页数,设定一个限度对一定大的索引进行整理 DECLARE @TmpName VARCHAR(150) -- Decide on the maximum fragmentation to allow SELECT @maxfrag = 30.0 SELECT @MaxScanDensity=70.0 SELECT @Page=400 -- Declare cursor DECLARE tables CURSOR FOR    SELECT TABLE_NAME    FROM INFORMATION_SCHEMA.TABLES    WHERE TABLE_TYPE = 'BASE TABLE' -- Create the table CREATE TABLE #fraglist (    ObjectName CHAR (255),    ObjectId INT,    IndexName CHAR (255),    IndexId INT,    Lvl INT,    CountPages INT,    CountRows INT,    MinRecSize INT,    MaxRecSize INT,    AvgRecSize INT,    ForRecCount INT,    Extents INT,    ExtentSwitches INT,    AvgFreeBytes INT,    AvgPageDensity INT,    ScanDensity DECIMAL,    BestCount INT,    ActualCount INT,    LogicalFrag DECIMAL,    ExtentFrag DECIMAL) -- Open the cursor OPEN tables -- Loop through all the tables in the database FETCH NEXT    FROM tables    INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN -- Do the showcontig of all indexes of the table    INSERT INTO #fraglist    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')       WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')         FETCH NEXT       FROM tables       INTO @tablename END -- Close and deallocate the cursor CLOSE tables DEALLOCATE tables -- Declare cursor for list of indexes to be defragged DECLARE indexes CURSOR FOR    SELECT ObjectName, ObjectId, IndexId, LogicalFrag    FROM #fraglist    WHERE (LogicalFrag >= @maxfrag OR [ScanDensity]<=@MaxScanDensity) AND [CountPages]>=@page       AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 -- Open the cursor OPEN indexes -- loop through the indexes FETCH NEXT    FROM indexes    INTO @tablename, @objectid, @indexid, @frag WHILE @@FETCH_STATUS = 0 BEGIN       --在线整理碎片       PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',       ' + RTRIM(@indexid) + ') - fragmentation currently '        + RTRIM(CONVERT(varchar(15),@frag)) + '%'    SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',        ' + RTRIM(@indexid) + ')'    EXEC (@execstr)       --更新统计信息       IF @TmpName<>@tablename    BEGIN         SET @tmpName=@tableName         EXEC('UPDATE STATISTICS '+@TableName)    END         FETCH NEXT       FROM indexes       INTO @tablename, @objectid, @indexid, @frag END -- Close and deallocate the cursor CLOSE indexes DEALLOCATE indexes -- Delete the temporary table DROP TABLE #fraglist END GO