Best Approach for Reindexing

Question

I am trying to reduce fragmentation in all of the indexes for a database running on SQL Server 2005. Currently I am trying to use ALTER INDEX in conjunction with sp_MSforeachtable, to apply it to all of the indexes for all of the tables:

sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD;"

But for some reason this doesn’t always seem to work? If I try it for a single index, or all of the indexes for a single table then the fragmentation is cleaned up, it just seems to be when I apply it to the whole database that I get problems.

Previously I might have used DBCC DBREINDEX but BOL states it will be removed in the next version of SQL Server, so I don’t want to use it.

Can anyone give me any advice on the best way to tackle cleaning up all of the indexes in a database?

Thanks

Answer 1

Check out the article and accompanying sample script to handle this task at SQL Fool (Michelle Ufford's website):

http://sqlfool.com/2009/06/index-defrag-script-v30/

This is quite a nice solution to handle this once and for all!

The best practice recommendation is to reorganize your index if you have 5-30% of fragmentation, and only rebuild it if it has more than 30% fragmentation. You can easily use these thresholds or specify your own using this script.

Marc

Answer 2

The safest and most portable way is to drop the indices and to re-add them.

Answer 3

If you want to fully automate your SQL Server Index maintenance then I seriously reccomend that you check out Michelle Ufford's stored procedure for this.

Index Degfrag Script V3.0

It is what I consider to be the best index maintenance script I have ever read.

One of the best features about this script are that you can customize the threshold values that you use in order to determine whether or not to REBUILD or REORGANIZE a given index strucutre.

It also provides the option to limit the number of CPU cores that are utilized by the procedure. An excellent option if you intend to run the script on a busy live production database.

Warning: As with all internet available code, be sure you test it thoroughly before using in a production environment. You will also most likely want to incorporate your own customisation and features too.

Answer 4

Or you can use Microsoft's index rebuilding script found here http://msdn.microsoft.com/en-us/library/ms188917.aspx

    -- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
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 @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
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.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        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;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

I use this script together with SQL Server Agent to automate the task. Hope this helps.