Script: How to Defragment All The Indexes In a Database

This blog post first appeared on SQLMaestros

In our previous blog post, we got to know, how to find out all fragmented indexes in a database. Now we will see how to defrag indexes.

According to Microsoft guidelines, based upon index fragmentation percentage, we may choose to either REORGANIZE, REBUILD indexes or just ignore. Here are the MS guidelines (Of course, you tweak the numbers according to your environment and available maintenance window).

Let’s say N = fragmentation percentage

N <= 5 – IGNORE
5 < N < 30 – REORGANIZE
N > 30 – REBUILD

Reorganize a fragmented index

USE [SQLMaestros]
GO
ALTER INDEX [PK__Subscrib__7DFEB63423B0DFD3] 
    ON [hol].[Subscribers] REORGANIZE
GO

Rebuilding a fragmented index

USE [SQLMaestros]
GO
ALTER INDEX [PK__Subscrib__7DFEB63423B0DFD3] 
    ON [hol].[Subscribers] REBUILD
GO

Rebuilding all indexes in a table

USE [SQLMaestros]
GO
ALTER INDEX ALL ON [hol].[Subscribers]
REBUILD;

You can replace REBUILD with REORGANIZE in the above query to reorganize all the indexes in a table.

Here comes our query of the day.

Rebuilding all indexes in a database

USE SQLMaestros
GO
DECLARE @NoOfPartitions BIGINT;
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @idxname NVARCHAR(255);
DECLARE @objname NVARCHAR(255);
DECLARE @partitionnum BIGINT;
DECLARE @schemaname NVARCHAR(255);
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @statement VARCHAR(8000);
-- checking existance of the table that we create for temporary purpose

IF OBJECT_ID('defrag_work', 'U') IS NOT NULL 
  DROP TABLE defrag_work; 


-- Copy the fragmented indexes data into defrag_work table
-- All the indexes that has fragmentation < 5 are getting stored into our work table
SELECT  [object_id] AS objectid ,
        index_id AS indexid ,
        partition_number AS partition_no ,
        avg_fragmentation_in_percent AS frag
INTO    defrag_work
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE   avg_fragmentation_in_percent >5.0 and index_id > 0;


-- cursor to process the list of partitions
DECLARE partitions CURSOR
FOR
    SELECT  *
    FROM    defrag_work;

-- Open the cursor.
OPEN partitions;

-- Looping through the partitions
FETCH NEXT
   FROM partitions
   INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0
    BEGIN;
        SELECT  @objname= QUOTENAME(so.name) ,
                @schemaname = QUOTENAME(ss.name)
        FROM    sys.objects AS so
                JOIN sys.schemas AS ss ON ss.schema_id = so.schema_id
        WHERE   so.object_id = @objectid;
        

        SELECT  @idxname = QUOTENAME(name)
        FROM    sys.indexes
        WHERE   object_id = @objectid
                AND index_id = @indexid;
                

        SELECT  @NoOfPartitions = COUNT(*)
        FROM    sys.partitions
        WHERE   object_id = @objectid
                AND index_id = @indexid;

/* 
Let’s say N = fragmentation percentage

N <= 5 = IGNORE
5 < N < 30 = REORGANIZE
N > 30 = REBUILD

*/
               
        IF (@frag < 30.0) -- @frag > 5 is already filtered in our first query, so we need that condition here
            BEGIN;
                SELECT  @statement = 'ALTER INDEX ' + @idxname + ' ON '
                        + @schemaname + '.' + @objname + ' REORGANIZE';
                IF @NoOfPartitions > 1
                    SELECT  @statement = @statement + ' PARTITION='
                            + CONVERT (CHAR, @partitionnum);
                EXEC (@statement);
            END;

        IF @frag >= 30.0
            BEGIN;
                SELECT  @statement = 'ALTER INDEX ' + @idxname + ' ON '
                        + @schemaname + '.' + @objname + ' REBUILD';
                IF @NoOfPartitions > 1
                    SELECT  @statement = @statement + ' PARTITION='
                            + CONVERT (CHAR, @partitionnum);
                EXEC (@statement);
            END;
        PRINT 'Executed ' + @statement;

        FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum,
            @frag;
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- drop the table
IF OBJECT_ID('defrag_work', 'U') IS NOT NULL 
  DROP TABLE defrag_work; 

The above script was originally written by Itzik ben-Gan.

See you soon with another script.

This post is part of The Scripts Garage. You can find all scripts at The Scripts Garage.