3VL – SQL Server Development

Further to my previous post Index Fragmentation you can use the following script to automatically rebuild and reorganize indexes.

I have it installed in an agent job that runs daily:

———————————————————————
– Rebuild the Indexes in EBS
———————————————————————

– Declare the Variables
DECLARE  @Table_ID  int,
   @Index_ID  int,
   @Pages   bigint,
   @Index_Name sysname,
   @Fragmentation float,
   @Table_Name sysname,
   @Alter   varchar(200)

– Declare the Cursor
DECLARE Indexes CURSOR FOR

SELECT  ID.object_id AS Table_ID
  ,ID.index_id AS Index_ID
  ,ID.page_count AS Pages
  ,I.name AS Index_Name
  ,ID.Avg_Fragmentation_In_Percent AS Fragmentation
  ,T.name AS Table_Name

FROM  sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS ID
JOIN  sys.indexes AS I WITH(NOLOCK)
ON   ID.Object_id = I.Object_id
 AND   ID.Index_id = I.Index_id
JOIN sys.objects AS T
ON  T.object_id = I.object_id
WHERE  I.name IS NOT NULL

OPEN Indexes
FETCH Indexes INTO @Table_ID,
     @Index_ID,
     @Pages,
     @Index_Name,
     @Fragmentation,
     @Table_Name 

– Start the Processing Loop
WHILE @@Fetch_Status = 0
   BEGIN

 IF @Fragmentation >30
 BEGIN
  set @alter = ‘ALTER INDEX ‘ + @Index_Name + ‘ ON ‘ + @Table_Name + ‘ REBUILD’;
  exec(@alter);
 END
 ELSE IF @Fragmentation >10
 BEGIN
  set @alter = ‘ALTER INDEX ‘ + @Index_Name + ‘ ON ‘ + @Table_Name + ‘ REORGANIZE’;
  exec(@alter);
 END

 – Perform Row-by-row Processing.
 – Get the Next Row
 FETCH Indexes INTO @Table_ID,
      @Index_ID,
      @Pages,
      @Index_Name,
      @Fragmentation,
      @Table_Name
 END

CLOSE Indexes

DEALLOCATE Indexes

RETURN

This entry was posted on Thursday, April 15th, 2010 at 11:49 am and is filed under DBA, Database Optimisation. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.