Defragmenting Indexes

The following SQL code is an example of a manual maintenance utility for defragmenting table indexes.

/*
Author:		Matthew Wells (GE) Date Created:	2006/02/21

Description:
=========
This routine checks the level of index fragmentation, then defragments the indexes and then reindexes them.

Change Date	Who	What
===========	====	=====
*/		

DECLARE @IndexList TABLE (	RowId	int IDENTITY PRIMARY KEY,
                                                      ObjectId int,
                                                      IndexId	int)

CREATE TABLE #Indexes (  RowId	           int IDENTITY PRIMARY KEY,
                                    ObjectName			       varchar(128),
                                    ObjectId		int,	
                                    IndexName			        varchar(128),
                                    IndexId			          int,
                                    Level			            int,
                                    Pages			            int,
                                    Rows			             int,
                                    MinimumRecordSize    int,	
                                    MaximumRecordSize    int,	
                                    AverageRecordSize	int,		
                                    ForwardedRecords	int,		
                                    Extents			          int,
                                    ExtentSwitches		int,	
                                    AverageFreeBytes	real,		
                                    AveragePageDensity		real,	
                                    ScanDensity			real,
                                    BestCount			int,
                                    ActualCount			int,
                                    LogicalFragmentation	real,		
                                    ExtentFragmentation		real)	

DECLARE	@ObjectName	varchar(128), 
              @ObjectId	 int,
              @IndexName	varchar(128),
              @IndexId	  int,
              @Debug	    int,
              @Defragment     int,
              @Reindex	 int,
              @Start_Time     datetime,
              @End_Time	datetime,
              @Time	    real,
              @Total_Start_Time	datetime, 
              @Total_End_Time		datetime, 
              @Total_Time			real,
              @Query_Time	             real,
              @Defrag_Time	            real,           
              @Reindex_Time	           real,
              @Rows	                   int,
              @Row	                    int,
              @ScanDensity	            float,
              @LogicalFragmentation	float,
              @DBCCCOMMAND		varchar(25), 
              @DBCCOPTIONS	  varchar(25),
             @SCANDENSITYLIMIT	float,
             @FRAGMENTATIONLIMIT	float

--	Initialization	--

-- Constants
SELECT @DBCCCOMMAND	     = 'DBCC SHOWCONTIG (', 
            @DBCCOPTIONS	= ') WITH TABLERESULTS', 
            @SCANDENSITYLIMIT  = 90.0,
            @FRAGMENTATIONLIMIT = 10.0

-- Parameters
SELECT @Defrag_Time	= 0,
          @Reindex_Time	         = 0, 
          @Total_Start_Time = getdate(),
          @Debug	                   = 1,
          @Defragment	              = 0, -- SET THIS TO 1 TO DEFRAGMENT INDEXES
          @Reindex	                 = 0 -- SET THIS TO 1 TO REBUILD INDEXES

--	Get Fragmented Indexes	--

SELECT @Start_Time = getdate() 
IF @Debug = 1
        BEGIN
        PRINT 'Querying Indexes...' 
        END
INSERT INTO @IndexList (  ObjectId,
SELECT si.id,
            si.IndID 
FROM sysindexes si
JOIN sysobjects so ON    so.name = si.name
                                             AND si.id = so.Parent_obj 
                                             AND (	so.xtype = 'PK'
                                                                    OR so.xtype = 'UQ')
WHERE si.IndID > 0
SELECT @Rows  = @@ROWCOUNT,
                @Row	= 0
WHILE @Row < @Rows 
      BEGIN
      SELECT @Row = @Row + 1
      SELECT @ObjectId = ObjectId,
                         @IndexId = IndexId
 
FROM @IndexList 
WHERE RowId = @Row
INSERT #Indexes (ObjectName,
 
                                           ObjectId, 
                                           IndexName, 
                                           IndexId, 
                                           Level, 
                                           Pages, 
                                           Rows,
                                           MinimumRecordSize, 
                                           MaximumRecordSize, 
                                           AverageRecordSize, 
                                           ForwardedRecords, 
                                           Extents, 
                                           ExtentSwitches, 
                                           AverageFreeBytes, 
                                           AveragePageDensity, 
                                           ScanDensity,
                                           BestCount, 
                                           ActualCount, 
                                           LogicalFragmentation, 
                                           ExtentFragmentation)
EXEC (@DBCCCOMMAND + @ObjectId + ',' + @IndexId + @DBCCOPTIONS) 
END
SELECT @Query_Time = convert(real, datediff(s, @Start_Time, getdate()))/60.0 
IF @Debug = 1
        BEGIN
        SELECT ObjectName,
               IndexName, 
               IndexId, 
               ScanDensity,
               LogicalFragmentation, 
               ExtentFragmentation
FROM #Indexes
WHERE ScanDensity < @SCANDENSITYLIMIT
                OR LogicalFragmentation > @FRAGMENTATIONLIMIT
ORDER BY	 ObjectName ASC, 
                      IndexName ASC
PRINT 'Queried Indexes in ' + ltrim(str(@Query_Time, 25, 2)) + ' min' 
END

--	Defragment Indexes	--

IF @Defragment = 1
BEGIN
SELECT @Row = 0
WHILE @Row < @Rows 
BEGIN
SELECT @Row = @Row + 1

SELECT  @ObjectName	             = ObjectName, 
        @ObjectId		                  = ObjectId,
        @IndexName	                       = IndexName,
        @IndexId	                         = IndexId,
        @ScanDensity		               = ScanDensity, 
        @LogicalFragmentation	      = LogicalFragmentation
FROM #Indexes 
WHERE RowId = @Row
IF	@ScanDensity < @SCANDENSITYLIMIT
        OR @LogicalFragmentation > @FRAGMENTATIONLIMIT 
        BEGIN
        IF @Debug = 1
                BEGIN
                PRINT 'Defragmenting ' + @ObjectName + '.' + @IndexName 
                END
SELECT @Start_Time = getdate() 
IF @Debug = 1
          BEGIN
          DBCC INDEXDEFRAG (0, @ObjectId, @IndexId) 
          END
 ELSE
          BEGIN
          DBCC INDEXDEFRAG (0, @ObjectId, @IndexId) WITH NO_INFOMSGS 
          END
 SELECT @Time = convert(real, datediff(s, @Start_Time, getdate()))/60.0 
 SELECT @Defrag_Time = @Defrag_Time + @Time
IF @Debug = 1
          BEGIN
          PRINT 'Defragmented ' + @ObjectName + '.' + @IndexName + ' in ' + ltrim(str(@Time, 25, 2)) + 

' min'
                        END
                  END
             END
      END

--	Rebuild Indexes	--
IF @Reindex = 1
BEGIN
SELECT @Row = 0

WHILE @Row < @Rows 
      BEGIN
      SELECT @Row = @Row + 1

SELECT @ObjectName	                 = ObjectName, 
             @IndexName		                = IndexName,
             @ScanDensity		   = ScanDensity, 
             @LogicalFragmentation	= LogicalFragmentation
FROM #Indexes 
WHERE RowId = @Row
IF	@ScanDensity < @SCANDENSITYLIMIT
         OR @LogicalFragmentation > @FRAGMENTATIONLIMIT 
         BEGIN
IF @Debug = 1
         BEGIN
         PRINT 'Reindexing ' + @ObjectName + '.' + @IndexName 
         END
SELECT @Start_Time = getdate() 
IF @Debug = 1
          BEGIN
          DBCC DBREINDEX (@ObjectName, @IndexName) 
          END
 ELSE
          BEGIN
          DBCC DBREINDEX (@ObjectName, @IndexName) WITH NO_INFOMSGS 
          END
 
SELECT @Time = convert(real, datediff(s, @Start_Time, getdate()))/60.0 
SELECT @Reindex_Time = @Reindex_Time + @Time
IF @Debug = 1
           BEGIN
           PRINT 'Reindexed ' + @ObjectName + '.' + @IndexName + ' in ' + ltrim(str(@Time, 25, 2)) + '
min'
            END
        END
    END
 END
 
--	End Game	--

SELECT @Total_Time = convert(real, datediff(s, @Total_Start_Time, getdate()))/60.0
IF @Debug = 1
        BEGIN
        PRINT 'Finished!'
        PRINT 'Query Time=' + ltrim(str(@Query_Time, 25, 2)) + ' min' 
        PRINT 'Defrag Time=' + ltrim(str(@Defrag_Time, 25, 2)) + ' min' 
        PRINT 'Reindex Time=' + ltrim(str(@Reindex_Time, 25, 2)) + ' min'
 
END

DROP TABLE #Indexes