Reorganize and Rebuild Indexes in SQL Server

Reorganize and Rebuild Indexes in SQL Server

Reorganize and Rebuild Indexes in SQL Server

Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance. Reorganizing and rebuilding indexes is a quick way to optimize your databases’ performance.

We periodically (daily, weekly, or monthly) perform database reorganization on all the indexes on all the tables in our databases and those of clients whom subscribe to our support and maintenance plans.

From time to time we run into folks who either reorganize OR rebuild indexes at any given time. Their reasoning  is that indexes should be rebuilt when they are over 30% fragmented and they should be reorganized when they are between 10% and 30% fragmented. Also, running both every time could result in a waste of system resources. We agree, however, we have resolved this problem by creating a script (below) that reorganizes and rebuilds indexes only if those conditions specified are met – in one single shot.

Finally, you might be wandering why we do not use SQL Server’s DBCC DBREINDEX command instead of using a custom script. The reason for this is because DBCC DBREINDEX will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables AND in all the databases within the running instance:

[code lang=”sql”]
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; a
DECLARE @partitions 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.
SET @dbid = Db_id();

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 (@dbid, NULL, NULL, NULL, N’LIMITED’)
WHERE avg_fragmentation_in_percent > 10.0 — Allow limited fragmentation
AND index_id > 0 — Ignore heaps
AND page_count > 25; — Ignore small tables

— Declare the cursor for the list of partitions to be processed.
SELECT objectid,
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;


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;


Now you can wrap this script within a SQL Server Maintenance Plan and have it run automatically at your desired time interval.

Antonio Chagoury

Hi, I'm Antonio, Founder and CEO of Maxiom Technology (formerly Inspector IT).I'm a technology executive and entrepreneur who has achieved consistent success in driving growth, generating revenue, and enhancing value in domestic and international markets through technology product innovations.

  • Yehuda Tiram
    Posted at 4:30 am, January 18, 2013

    I tried it and it works verey good.
    The code here shows an extra a in line 9 that has to be removed though.
    Is there a way to issue a report of the fragmantation (to see before and after)
    And the copy paste does not wotk BTW.

    Thanks for sharing,

  • Rick Reszler
    Posted at 2:47 pm, August 15, 2013

    Does this script automatically run on all databases when its setup a Execute TSQL task in a SQL maintenance plan or does some additional code need to be added?

  • Alex
    Posted at 6:02 pm, November 14, 2013


    Awesome code.

    Have a question, in the case I would like to target this to a specific database, should I change the line

    FROM sys.Dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, N’LIMITED’)


    FROM sys.Dm_db_index_physical_stats (db_id(‘Adventure_Works’), NULL, NULL, NULL, N’LIMITED’)



  • Seyit
    Posted at 10:38 am, November 18, 2013

    Thnx for this useful sharing.
    It helped so much for our index maintenance task…

  • jalabop
    Posted at 5:00 pm, May 11, 2014

    Hi Antonio.

    I’ve been looking for a solution such as your script. In every other examples to go through all databases a cursor is needed. I’m not sure if I’m doing it right. I’ve copied your code into a Maintenance Plan — TSQL statement and it seems that it’s only executed in the default database (master in my case). Does some additional code need to be added?


  • Posted at 11:50 am, September 17, 2014
  • Rick
    Posted at 12:08 pm, February 5, 2015

    This doesn’t seem to work on SQL 2012.

    Msg 208, Level 16, State 1, Line 19
    Invalid object name ‘sys.Dm_db_index_physical_stats’.