logo

Premium Support
24/7/365

Premium support customers may submit help-desk tickets using the form below.
Alternatively, the help-desk system can be accessed directly at support.maxiomtech.com. support@maxiomtech.com
info@maxiomtech.com
+1 (703) 942-9420
 

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:

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 @frag FLOAT;
DECLARE @command NVARCHAR(4000);
DECLARE @dbid SMALLINT;

-- 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,
       page_count
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.
DECLARE partitions CURSOR FOR
  SELECT objectid,
         indexid,
         partitionnum,
         frag
  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

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

mm
Antonio Chagoury

Founder and CEO

8 Comments
  • 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,
    Yehuda

  • 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

    Hi!

    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’)

    To

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

    ???

    Thanks!

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

    Hi,
    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?

    Thanks!

  • 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’.