SQL Server Index Defragmentation

This script was written for a PeopleSoft production database but can be used on any database.  It rebuilds indexes with some intelligence built in, and logs 90 days of work to a table for reference.  It doesn't bother with indexes that are very small (less than 100 pages) since these can often incorrectly show fragmentation due to their size.  You can change the fragmentation percentages to fine tune when indexes are reorganized or rebuilt.

-- Defragment all indexes with more than 100 pages.  
-- If the index has more than 10% but less than 30% fragmentation we reorganize:
--  'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'
--if the index has more than 30% fragmentation we rebuild it (online if we can, if not we do it offline):
--   'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'
/*        
DROP TABLE DBA_index_fragmentation        

CREATE TABLE DBA_index_fragmentation (        
datetime_entered datetime,        
objectname nvarchar(600),        
indexname nvarchar(600),        
avg_fragmentation_in_percent float,        
page_count bigint,        
rebuild_type nvarchar(300))  
*/
DELETE FROM dba_index_fragmentation
WHERE  datetime_entered < Getdate() - 90;

-- Ensure a USE  statement has been executed first.        
SET nocount ON;

DECLARE @objectid BIGINT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @pagecount BIGINT;
DECLARE @command NVARCHAR(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function         
-- and convert 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,
       page_count                   AS pagecount
INTO   #work_to_do_online
FROM   sys.Dm_db_index_physical_stats (Db_id(), NULL, NULL, NULL, 'LIMITED')
WHERE  avg_fragmentation_in_percent > 10.0
       AND index_id > 0
       AND page_count > 100
-- MASHTON - 04/15/2011 - Limit the work to reasonably large indexes (about 10,000 rows of data or more)        
ORDER  BY Object_name(object_id);

-- MASHTON - 4/20/2011 - MOVE INDEXES WITH SPECIAL COLUMNS INTO A SEPARATE WORK TABLE.  THESE INDEXES MUST BE REBUILT OFFLINE        
SELECT *
INTO   #work_to_do_offline
FROM   #work_to_do_online A
WHERE  EXISTS (SELECT 'X'
               FROM   information_schema.columns C
               WHERE  Object_name(A.objectid) = C.table_name
                      AND ( C.data_type IN ( 'text', 'ntext', 'image', 'xml' )
                             OR ( C.data_type IN ( 'varchar', 'nvarchar',
                                                   'varbinary' )
                                  AND C.character_maximum_length = -1 ) )
              -- this checks for 'varchar(max)', 'nvarchar(max)', 'varbinary(max)'        
              )

-- MASHTON - 4/20/2011 - REMOVE THE INDEXES THAT WE JUST MOVED TO THE OTHER "OFFLINE" TABLE ABOVE        
DELETE FROM #work_to_do_online
WHERE  EXISTS (SELECT 'X'
               FROM   information_schema.columns C
               WHERE  Object_name(#work_to_do_online.objectid) = C.table_name
                      AND ( C.data_type IN ( 'text', 'ntext', 'image', 'xml' )
                             OR ( C.data_type IN ( 'varchar', 'nvarchar',
                                                   'varbinary' )
                                  AND C.character_maximum_length = -1 ) )
              
-- this checks for 'varchar(max)', 'nvarchar(max)', 'varbinary(max)'        
              )

-- **********************************************************************************        
-- ** PROCESS THESE INDEXES ONLINE IF A REBUILD IS NEEDED  **************************        
-- **********************************************************************************        
-- Declare the cursor for the list of partitions to be processed.        
DECLARE partitions CURSOR FOR
  SELECT *
  FROM   #work_to_do_online;

-- Open the cursor.        
OPEN partitions;

-- Loop through the partitions.        
WHILE ( 1 = 1 )
  BEGIN;
      FETCH next FROM partitions INTO @objectid, @indexid, @partitionnum, @frag,
      @pagecount;

      IF @@FETCH_STATUS < 0
        BREAK;

      SELECT @objectname = Quotename(o.NAME),
             @schemaname = Quotename(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 = Quotename(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
        SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                       + @schemaname + N'.' + @objectname + N' REORGANIZE';

      IF @frag >= 30.0
        --SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE = ON)';        
        SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                       + @schemaname + N'.' + @objectname + N' REBUILD';

      IF @partitioncount > 1
        SET @command = @command + N' PARTITION='
                       + Cast(@partitionnum AS NVARCHAR(10));

      EXEC (@command);

      --PRINT N'Executed: ' + @command;        
      INSERT INTO dba_index_fragmentation
      VALUES      (Getdate(),
                   @objectname,
                   @indexname,
                   @frag,
                   @pagecount,
                   'ONLINE (testing offline) Rebuild')
  END;

-- Close and deallocate the cursor.        
CLOSE partitions;

DEALLOCATE partitions;

-- **********************************************************************************        
-- ** PROCESS THESE INDEXES OFFLINE IF A REBUILD IS NEEDED  *************************        
-- **********************************************************************************        
-- Declare the cursor for the list of partitions to be processed.        
DECLARE partitions CURSOR FOR
  SELECT *
  FROM   #work_to_do_offline;

-- Open the cursor.        
OPEN partitions;

-- Loop through the partitions.        
WHILE ( 1 = 1 )
  BEGIN;
      FETCH next FROM partitions INTO @objectid, @indexid, @partitionnum, @frag,
      @pagecount;

      IF @@FETCH_STATUS < 0
        BREAK;

      SELECT @objectname = Quotename(o.NAME),
             @schemaname = Quotename(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 = Quotename(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
        SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                       + @schemaname + N'.' + @objectname + N' REORGANIZE';

      IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                       + @schemaname + N'.' + @objectname + N' REBUILD';

      IF @partitioncount > 1
        SET @command = @command + N' PARTITION='
                       + Cast(@partitionnum AS NVARCHAR(10));

      EXEC (@command);

      --PRINT N'Executed: ' + @command;        
      INSERT INTO dba_index_fragmentation
      VALUES      (Getdate(),
                   @objectname,
                   @indexname,
                   @frag,
                   @pagecount,
                   'OFFLINE Rebuild')
  END;

-- Close and deallocate the cursor.        
CLOSE partitions;

DEALLOCATE partitions;

-- *****************************************************************
-- ** END INDEX PROCESSING             *****************************        
-- *****************************************************************        
-- Drop the temporary tables.        
DROP TABLE #work_to_do_online;

DROP TABLE #work_to_do_offline;