Problem
How can I reorganize or rebuild my database indexes on a SQL 2005 server to improve performance?

Environment
Microsoft SQL Server 2005

Cause
SQL Server automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data in a table. This also forces changes to the index that have to create new index pages to accommodate the additional data.  As pages are added to an index, they use whatever SQL page that is available. This growth can cause the data in the index to become fragmented,  that is that the index data becomes physically scattered and out of order. When an index becomes fragmented, performance is diminished. 

Additionally over time as index pages split, the B-Tree of the index becomes unbalanced, that is the number of index pages needed to find a specific value is higher for some values than for others thus making some portions of an index a lot slower.

Resolution

SQL Server can remedy index fragmentation by either reorganizing an index or by rebuilding(recommended) an index.  Defragmenting (reorganize) an index consolidates all of the index data (or leaf level) into the smallest number of pages possible while still respecting the indexes fill factor and makes sure that those pages are physically in the order as per the index. Defragmentation can be done while the index is actively being used and has minimal overhead to SQL's performance.  The Reorganize index option, however does not have the ability to rebuild the indexes non-leaf level pages.  Only rebuilding an index causes the index's B-Tree to be rebuilt to ensure that it is balanced.  The downside is that unlike reorganizing an index, while rebuilding, the index is unavailable and SQL must retrieve requested data without the use of the index currently being rebuilt.

A general best practice guideline in a typical environment is to schedule a SQL maintenance plan to rebuild the indexes once a week. That maintenance plan is configured to leave 10% free space in the index after the rebuild. Also the same maintenance plan should be configured to update statistics.  This maintenance plan should be scheduled to run during low utilization so minimize the impact of an index being unavailable while it is being rebuilt.
See KB40488 for a step by step example.

In some cases, a manual SQL execution is needed to do a one time rebuild or defragmentation of the indexes.  In SQL 2005 there is a system function called sys.dm_db_index_physical_stats that can provide fragmentation information for each index. Listed below are a few different SQL queries that will help with analyzing and manually correcting fragmented indexes. Each of them have comments that describe what they are used for.

SQL script to show index fragmentation of a database for greater than 10%:

select
 [Table] = object_name(stats.[object_id])
,[Index]= b.[name]
,[Avg Fragmentation] = cast(avg_fragmentation_in_percent as numeric (6,4))
,[Page Count] = stats.page_count
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats
join sys.indexes as b on stats.object_id = b.object_id
 and stats.[index_id] = b.[index_id]
where avg_fragmentation_in_percent > 10.0 and b.index_id > 0
order by 1, 2;

---

This SQL query adds Solutions and dataclass types:

-- SQL script to show index fragmentation of a database for greater than 10%

declare @CompatMode int
 select @CompatMode = ( 
           select cmptlevel 
             from master.dbo.sysdatabases 
            where dbid = db_id() 
          )

if (@CompatMode = 90) begin

   select
            [Solution] = isnull(vp.[Name],'NS Core')
           ,[Table] = object_name(stats.[object_id])
           ,[Type] = case cast(dc.[ClassType] as int)
                       when '0' then 'User'
                       when '1' then 'Event'
                       when '2' then 'Common'
                       else 'Core'
                      end
           ,[Index]= b.[name]
           ,[Avg Fragmentation] = cast(avg_fragmentation_in_percent as numeric (6,4))
           ,[Page Count] = stats.[page_count]

    from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') stats

    join sys.indexes b on stats.object_id = b.object_id
     and stats.[index_id] = b.[index_id]

    left join [DataClass] dc on dc.[DataTableName] = object_name(stats.[object_id])
      or dc.[HistoryTableName] = object_name(stats.[object_id])

    left join vItem vi on vi.[guid] = dc.[guid]

    left join vProduct vp on vp.[guid] = vi.[ProductGuid]

   where avg_fragmentation_in_percent > 10.0 and b.index_id > 0
     and stats.[page_count] > 2

   order by 5 desc, 1, 3, 4;
end

-- Shows database fragmentation with a page count variable to narrow the results
-- Also a SQL quick script to defrag one index at a time if needed

declare @PageCount bigint
    set @PageCount = 1000

select
       [Table] = object_name(stats.[object_id])
       ,[Index]= si.[name]
       ,[Avg Fragmentation] = cast(stats.[avg_fragmentation_in_percent] as numeric (6,4))
       ,[Page Count] = stats.[page_count]
       ,[SQL script] = case
          when (stats.[avg_fragmentation_in_percent] < = 30)
               then 'alter index ['+ si.[name] +'] on ['+ object_name(stats.[object_id]) +'] reorganize'
          when (stats.[avg_fragmentation_in_percent] > 30)
               then 'alter index ['+ si.[name] +'] on ['+ object_name(stats.[object_id]) +'] rebuild'
          end
  from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats

  join sys.indexes as si on stats.object_id = si.object_id

   and stats.[index_id] = si.[index_id]

 where stats.[avg_fragmentation_in_percent] > 10.0
   and si.index_id > 0
   and stats.[page_count] >= @PageCount

 order by 3 desc, 1, 2;

Note: You can copy the "SQL script" column and run them one at a time.

After you know the degree of fragmentation, you will need to determine the best method to correct the index fragmentation.

Fragmentation of > 10% and <= 30% will use this method:

alter index Index_Name on Table_Name reorganize

Fragmentation of > 30% will use this method.

alter index Index_Name on Table_Name rebuild

The following SQL script can automate the defragmentation and determine the method used. This will also show the change in fragmentation after the defragmentation process. 

NOTE: This script is not recommended to be used on a repeated schedule as it will always keep indexes defragmented to the point where they never get rebuilt.  Although this does keep Fragmentation cost down, the B-Tree will continue to become more and more unbalanced which will cause some portions of the index to become very slow. 

-- Warning: This is to be used ON SQL 2005 server
-- This SQL script uses SQL 2005 functionality
-- Ensure a USE <databasename> statement has been executed first.
-- Example: USE Altiris

DECLARE @CompatMode INT
 SELECT @CompatMode =
        (
            SELECT cmptlevel
              FROM master.dbo.sysdatabases
             WHERE dbid = db_id()
        )

IF (@CompatMode = 90) BEGIN
       DECLARE @CurrentRow INT;
       DECLARE @MaxRows  INT;
       DECLARE @ObjectId INT;
       DECLARE @ObjectName nvarchar(130);
       DECLARE @IndexId  INT;
       DECLARE @IndexName nvarchar(130);
       DECLARE @PartitionNum bigint;
       DECLARE @Partitions bigint;
       DECLARE @PartitionCount bigint;
       DECLARE @SchemaName nvarchar(130);
       DECLARE @AvgFrag numeric (6,4);
       DECLARE @SQLCommand nvarchar(4000);

       -- Beginning Index Fragmentation
       SELECT
              [Table] = object_name(stats.[object_id])
              ,[Index]= b.[name]
              ,[Avg Fragmentation] = CAST(avg_fragmentation_in_percent AS numeric (6,4))
              ,[Page Count] = stats.page_count
         INTO #InsIndexFrag
         FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL , NULL, 'LIMITED') AS stats
         JOIN sys.indexes AS b ON stats.object_id = b.object_id
          AND stats.[index_id] = b.[index_id]
        WHERE avg_fragmentation_in_percent > 10.0 AND b.index_id > 0;
  
       CREATE TABLE #DefregList
           (
                  [id] bigint identity (1,1) not null
                 ,[ObjectId] INT
                 ,[IndexId] INT
                 ,[PartitionNum] INT
                 ,[AvgFrag] FLOAT
              )

       -- Create temp table to hold needed values
       INSERT INTO #DefregList ([ObjectId], [IndexId], [PartitionNum],[AvgFrag])
       SELECT
              ObjectId = object_id
             ,IndexId = index_id
             ,[PartitionNum] = partition_number
             ,[AvgFrag] = avg_fragmentation_in_percent
         FROM sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED')
        WHERE avg_fragmentation_in_percent > 10.0
          AND index_id > 0

       -- Set inc values used in the loop
       SELECT @CurrentRow = 1
       SELECT @MaxRows = (SELECT max([id]) FROM #DefregList)

       -- Loop through the partitions and defrag or rebuild indexes.
       while (@CurrentRow <= @MaxRows) BEGIN
              -- Set values FROM the current row
              SELECT
                      @ObjectId = [ObjectId]
                     ,@IndexId = [IndexId]
                     ,@PartitionNum = [PartitionNum]
                     ,@AvgFrag = [AvgFrag]
                FROM #DefregList
               WHERE [id] = @CurrentRow

              -- Set object name, schema name, index id by using the object id
              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;

              -- The 30% is an arbitrary decision point at which to
              -- switch between reorganizing and rebuilding.
              IF (@AvgFrag < 30.0)
                     SET @SQLCommand =
                     N'ALTER INDEX ' + @IndexName +
                     N' ON ' + @SchemaName + N'.' + @ObjectName + N' REORGANIZE';
              IF (@AvgFrag >= 30.0)
                     SET @SQLCommand =
                     N'ALTER INDEX ' + @IndexName +
                     N' ON ' + @SchemaName + N'.' + @ObjectName + N' REBUILD';
              IF (@PartitionCount > 1)
                     SET @SQLCommand = @SQLCommand + N' PARTITION=' + CAST(@PartitionNum AS nvarchar(10));
              EXECUTE (@SQLCommand);
              PRINT N'Executed: ' + @SQLCommand;
              -- inc current row
              SET @CurrentRow = @CurrentRow + 1
       END
       -- Ending Index Fragmentation

       SELECT
               [Table] = object_name(stats.[object_id])
              ,[Index]= b.[name]
              ,[Avg Fragmentation] = CAST(avg_fragmentation_in_percent AS numeric (6,4))
              ,[Page Count] = stats.page_count
         INTO #FinIndexFrag
         FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL , NULL, 'LIMITED') AS stats
         JOIN sys.indexes AS b ON stats.object_id = b.object_id
          AND stats.[index_id] = b.[index_id]
        WHERE avg_fragmentation_in_percent > 10.0 AND b.index_id > 0;

  -- Show Fragmentation Change
       SELECT
              [Solution] = isnull(vp.[Name],'NS Core')
              ,[Table Name] = todo.[Table]
              ,[Type] = CASE CAST(dc.[ClassType] AS INT)
                          WHEN '0' THEN 'User'
                          WHEN '1' THEN 'Event'
                          WHEN '2' THEN 'Common'
                          ELSE 'Core'
                    end
              ,[Index Name] = todo.[Index]
              ,[Start %] = todo.[Avg Fragmentation]
              ,[End %] = did.[Avg Fragmentation]
              ,[Delta] = todo.[Avg Fragmentation] - did.[Avg Fragmentation]
              ,[Page Start(8KB)] = todo.[Page Count]
              ,[Page End] = did.[Page Count]
              ,[Page Delta] = todo.[Page Count] - did.[Page Count]
         FROM #InsIndexFrag todo
         JOIN #FinIndexFrag did ON todo.[Table]= did.[Table]
          AND todo.[Index] = did.[Index]
         LEFT JOIN [DataClass] dc ON dc.[DataTableName] = todo.[Table]
           OR dc.[HistoryTableName] = todo.[Table]
         LEFT JOIN vItem vi ON vi.[guid] = dc.[guid]
         LEFT JOIN vProduct vp ON vp.[guid] = vi.[ProductGuid]
        ORDER BY 5 desc, 1, 2;

       -- Drop the temporary table.
       DROP TABLE #DefregList;
       DROP TABLE #InsIndexFrag;
       DROP TABLE #FinIndexFrag;
END

Note: You may need to stop the Altiris service before you run the SQL script.