Fragmentation in SQL Server 2005

Fragmentation can directly impact performance in a SQL Server 2005 database. It is therefore critical to maintain indexes\tables via regular maintenance.

For more details on causes of Fragmentation see

One of my most useful scripts is to get fragmentation reports for individual tables:

DECLARE @object_id INT;


SET @db_id = DB_ID(N‘Futureproof’);
SET @object_id = OBJECT_ID(N‘Futureproof.dbo.Order_Insert’);


IF @db_id IS NULL
    PRINT N‘Invalid database’;
ELSE IF @object_id IS NULL
    PRINT N‘Invalid object’;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , ‘LIMITED’);
Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes. Taken from

Ensure a USE <databasename> statement has been executed first.
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.
    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)
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(, @schemaname = QUOTENAME(
        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;


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


— Drop the temporary table.
DROP TABLE #work_to_do;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: