SQL Server索引维护指导

 http://database.ctocio.com.cn/tips/69/8078069.shtml

 

SQL Server索引维护指导
作者：王红波
摘要
本文以笔者在实际工作中对SQL Server数据库种索引维护的思路和方法为导向，为大家介绍SQL Server索引维护相关的知识和方法。
导言
索引在数据库相关工作者的日常工作中占据了很重要的位置，索引需要牵涉到索引创建、优化和维护多方面的工作，本文以实例结合相关原理来介绍索引维护相关的知识。文中的相关代码，也可以满足多数情况下索引的维护需求。
实现步骤
1 .    以什么标准判断索引是否需要维护？
2 .    索引维护的方法有哪些？
3 .    能否方便地整理出比较通用的维护过程，实现自动化维护？

一、    以什么标准判断索引是否需要维护？
由于本文集中讨论索引维护相关，所以我们暂且抛开创建的不合理的那些索引，仅从维护的角度来讨论。从索引维护的角度来讲，最主要的参考标准就是索引碎片的大小。通常情况下，索引碎片在10 % 以内，是可以接受的。下面介绍获取索引碎片的方法：
    
SQL Server2000:  DBCC  SHOWCONTIG
SQL Server2005: sys.dm_db_index_physical_stats

实例(取db_test数据库所有索引碎片相关信息)：
SQL Server2000:
    
USE   [ db_test ] ;
GO
DBCC  SHOWCONTIG  WITH  TABLERESULTS, ALL_INDEXES
GO

SQL Server  2005 :
DECLARE   @db_name   VARCHAR ( 256 )
SET   @db_name = ' db_test '   
SELECT  
             db_name (a.database_id)  [ db_name ] ,
            c.name  [ table_name ] , 
            b.name  [ index_name ] , 
            a.avg_fragmentation_in_percent
FROM  
            sys.dm_db_index_physical_stats ( DB_ID ( @db_name ),  NULL , NULL ,  NULL ,  ' Limited ' )  AS  a
     JOIN  
            sys.indexes  AS  b  ON  a. object_id   =  b. object_id   AND  a.index_id  =  b.index_id
     JOIN
            sys.tables  AS  c  ON  a. object_id   =  c. object_id
WHERE  
        a.index_id > 0  
         AND  a.avg_fragmentation_in_percent > 5   - –碎片程度大于5

二、    索引维护的方法有哪些？
注：维护方式的选择，一方面要考虑是否是联机维护，另一方面就是速度上的考虑。一般碎片 <= 30 % 时，使用重新组织的方法速度比索引重建快；碎片 > 30 % 时，索引重建的速度比重新组织要快。

1 .    联机维护
SQL Server2000: 
DBCC  INDEXDEFRAG 重新组织索引，占用资源少，锁定资源周期短，可联机进行。

SQL Server  2005 :
1 .    联机重新组织：
ALTER   INDEX    [ index_name ]   ON   [ table_name ]
REORGANIZE;
2 .    联机重建：
ALTER   INDEX   [ index_name ]   ON   [ table_name ]
REBUILD  WITH  ( FILLFACTOR   =   85 , SORT_IN_TEMPDB  =   OFF ,
              STATISTICS_NORECOMPUTE  =   ON ,ONLINE  =   ON );

2 .    脱机维护
SQL Server2000： DBCC  DBREINDEX 
SQL Server  2005 ： ALTER   INDEX   [ indexname ]    ON    [ table_name ]  REBUILD;
                         CREATE   INDEX   WITH  DROP_EXISTING

3 .    能否方便地整理出比较通用的维护过程，实现自动化维护？
a)    获取及查看所有索引的碎片情况
SQL Server2000:
/**/ /*
描述：获取服务器上所有数据库的逻辑碎片率>5的索引信息
适用：SqlServer2000以后版本
*/
SET  NOCOUNT  ON
DECLARE   @db_name   varchar ( 128 )
DECLARE   @tablename   varchar ( 128 )
DECLARE   @table_schema   varchar ( 128 )
DECLARE   @execstr     varchar ( 255 )
DECLARE   @objectid    int
DECLARE   @indexid     int
DECLARE   @frag        decimal
DECLARE   @maxfrag     decimal
DECLARE   @sql         varchar ( 8000 )
--  Decide on the maximum fragmentation to allow for.
SELECT   @maxfrag   =   5

--  Create the table.
if   not   exists ( select   1   from  sys.tables  where  name  =   ' dba_manage_index_defrag ' )
create   table  dba_manage_index_defrag
( [ db_name ]   varchar ( 255 )
, [ table_name ]   varchar ( 255 )
, [ index_name ]   varchar ( 255 )
,avg_fragmentation_in_percent  real
,write_time  datetime   default   getdate ()
)
if   not   exists ( select   1   from  dbo.sysobjects  where  name  =   ' dba_manage_index_defrag_temp ' )
CREATE   TABLE  dba_manage_index_defrag_temp (
    [ db_name ]   char ( 255 )  default   '' ,
   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 )

--  Declare a cursor.
DECLARE  databases  CURSOR   FOR
    select  
        name 
     from  
        master.dbo.sysdatabases 
     where  
        dbid > 4

--  Open the cursor.
open  databases
fetch  databases  into   @db_name
while  ( @@fetch_status = 0 )
begin
     insert   into  dba_manage_index_defrag_temp 
    (ObjectName ,
   ObjectId ,
   IndexName,
   IndexId ,
   Lvl ,
   CountPages ,
   CountRows ,
   MinRecSize ,
   MaxRecSize ,
   AvgRecSize ,
   ForRecCount ,
   Extents ,
   ExtentSwitches ,
   AvgFreeBytes ,
   AvgPageDensity ,
   ScanDensity ,
   BestCount ,
   ActualCount ,
   LogicalFrag ,
   ExtentFrag )
     exec ( ' use [ ' + @db_name + ' ]; 
          dbcc showcontig 
         with 
            FAST, 
            TABLERESULTS, 
            ALL_INDEXES, 
            NO_INFOMSGS ' )
    
     update  
            dba_manage_index_defrag_temp
     set
             [ db_name ]   =   @db_name
     where    
             [ db_name ]   =   ''
     fetch   next   from  databases  into   @db_name
end

close  databases
deallocate  databases
insert   into  dba_manage_index_defrag
    ( [ db_name ]  
    , [ table_name ]  
    , [ index_name ]  
    ,avg_fragmentation_in_percent 
    )
select  
     [ db_name ] ,
    ObjectName  [ table_name ] ,
    indexname  [ index_name ] ,
    LogicalFrag  [ avg_fragmentation_in_percent ]  
from  
    dba_manage_index_defrag_temp 
where  
    logicalfrag > 5
--  Delete the temporary table.
DROP   TABLE  dba_manage_index_defrag_temp

GO
SELECT   *   FROM  dba_manage_index_defrag   -- 查看结果

SQL Server2005:
/**/ /*
描述：只显示逻辑碎片率大于5%的索引信息
限制：针对SqlServer2005以后版本。
功能：对数据库服务器所有非系统数据库进行索引碎片检查
        返回碎片率>5%的索引信息
*/
create   proc  p_dba_manage_get_index_defrage
as
set  nocount  on  
if   not   exists ( select   1   from  sys.tables  where  name  =   ' dba_manage_index_defrag ' )
create   table  dba_manage_index_defrag
( [ db_name ]   varchar ( 255 )
, [ table_name ]   varchar ( 255 )
, [ index_name ]   varchar ( 255 )
,avg_fragmentation_in_percent  real
,write_time  datetime   default   getdate ()
)

declare   @db_name   nvarchar ( 40 )
set   @db_name   =   ''
    
declare  cur_db_name  cursor   for  
     select  
        name 
     from  
        sys.databases
     where  
        database_id  >   4   and  state  =   0

open  cur_db_name
fetch  cur_db_name  into   @db_name
while  ( @@fetch_status = 0 )
begin
    
     insert   into  dba_manage_index_defrag
            ( [ db_name ]
            ,table_name
            ,index_name
            ,avg_fragmentation_in_percent)
     SELECT  
             db_name (a.database_id)  [ db_name ] ,
            c.name  [ table_name ] , 
            b.name  [ index_name ] , 
            a.avg_fragmentation_in_percent
     FROM  
            sys.dm_db_index_physical_stats ( DB_ID ( @db_name ),  null , NULL ,  NULL ,  ' Limited ' )  AS  a
     JOIN  
            sys.indexes  AS  b  ON  a. object_id   =  b. object_id   AND  a.index_id  =  b.index_id
     join  
            sys.tables  as  c  on  a. object_id   =  c. object_id
     where  
        a.index_id > 0  
         and  a.avg_fragmentation_in_percent > 5
fetch   next   from  cur_db_name  into   @db_name
end

CLOSE  cur_db_name
DEALLOCATE  cur_db_name

GO
select   *   from  dba_manage_index_defrag –查看结果

b)    根据索引碎片的情况自动选择合适的处理方法

针对Sql Server2000的联机维护：
/**/ /*Perform a 'USE <database name>' to select the database in which to run the script.*/
--  Declare variables
SET  NOCOUNT  ON ;
DECLARE   @tablename   varchar ( 128 );
DECLARE   @execstr     varchar ( 255 );
DECLARE   @objectid    int ;
DECLARE   @indexid     int ;
DECLARE   @frag        decimal ;
DECLARE   @maxfrag     decimal ;

--  Decide on the maximum fragmentation to allow for.
SELECT   @maxfrag   =   30.0 ;

--  Declare a cursor.
DECLARE  tables  CURSOR   FOR
    SELECT  TABLE_SCHEMA + ' . ' + TABLE_NAME  -- MSDN上面直接使用TABLE_NAME，如果SCHEMA不是DBO就会出错
    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 the cursor for the list of indexes to be defragged.
DECLARE  indexes  CURSOR   FOR
    SELECT  ObjectName, ObjectId, IndexId, LogicalFrag
    FROM  #fraglist
    WHERE  LogicalFrag  >=   @maxfrag
       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 );

    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;
GO


针对SQL Server2000的脱机维护：

sp_msforeachtable  @command1 = " dbcc  dbreindex( ' ? ' , '' , 85 )"

针对SQL Server2005的通用维护过程
(碎片小于30 % 的联机组织，碎片 >= 30 % 的脱机重建)：
--  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   and   @frag > 5
     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

总结
    索引的维护是有参考依据的，应该根据具体的碎片情况以及是否需要联机操作等需求，采用合理的维护方法。自动化的索引维护策略是可行的。