Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to Manage SQL Server Rebuilds and Reorganize for Index Fragmentation


By:   |   Read Comments (12)   |   Related Tips: More > Fragmentation and Index Maintenance


SQL Server Conference Giveaway - click to learn more


Problem

Indexes are one of the main database objects in SQL Server that massively contribute towards performance. By using the proper indexes, you can avoid fully scanning millions of records in tables in order to find what you are looking for. Instead of scanning the tables, you can traverse the index tree (index seek operation) and find what you are looking for much faster.

Even though indexes are very handy and necessary in performant database designs, they need maintenance. One of the reasons for that is fragmentation. The SQL Server Database Engine automatically maintains indexes whenever insert, update or delete modifications take place. However, over time and especially when heavy data modifications take place, index data becomes scattered in the database and thus fragmented. This fragmentation affects the performance of the index seek process, because these indexes do not have the exact logical ordering with the physical ordering inside the database's data file(s).

One way to handle this issue is by rebuilding fragmented indexes. If you do this using a Maintenance Plan, you will rebuild every single index in the database instead of rebuilding only the fragmented indexes (this has changed in SQL Server 2016). This tip provides a fully parameterized T-SQL script which identifies only the fragmented indexes in a database or in all databases in a SQL Server instance, and either generates a report with the defragmentation commands for review and controlled execution, or directly reorganizes or rebuilds the fragmented indexes based on Microsoft's recommendations. The script also supports AlwaysOn AG-enabled SQL Server instances.

Solution

Before presenting the script, let's first discuss a bit about the process of rebuilding indexes. First of all, never rebuild indexes that do not need to be rebuilt. As per Microsoft's recommendation, when an index has an average fragmentation percentage (use sys.dm_db_index_physical_stats to get these stats) larger than 5% and smaller or equal to 30% you do not need to rebuild the index. In this case, you just need to reorganize it. When the index has an average fragmentation percentage over 30%, then yes, you need to rebuild it. In more modern SQL Server setups, like for example AlwaysOn Availability Groups, you must make additional checks in your index maintenance scripts. For example you must check if a database is the primary replica or not. If it is the primary replica then you can proceed with the index maintenance.

The T-SQL Script presented in this tip handles all the above. You can run it on clustered SQL Server instances, AlwaysOn Availability Group enabled instances, etc. It has built-in logic that based on Microsoft's recommendations dynamically generates reorganization or rebuild statements only for the fragmented indexes.

SQL Server Index Rebuild and Reorganize Script

The script uses the following parameters:

@reportOnly (required)
Values:
- 0: The script will reorganize or rebuild the fragmented indexes.
- 1: The script will just output the index reorganization or rebuild commands without running them.

@databaseToCheck (optional)
Values:
- NULL: It will scan all databases with compatibility level SQL Server 2005 (90) or later for fragmented indexes.
- 'DatabaseName': It will scan only the given database for fragmented indexes.

@fragmentationThreshold (required)
Description: It maintains only the indexes that have average fragmentation percentage equal or higher from the given value.
Value Range: 5-100

@indexFillFactor (required)
Description: The percentage of the data page to be filled up with index data.
Recommended Value Range: 90-100

@indexStatisticsScanningMode (required)
Description: The scanning mode for index statistics
Available Values: 'DEFAULT', NULL, 'LIMITED', 'SAMPLED', or 'DETAILED'.
Recommended Value: 'SAMPLED'

@sortInTempdb (required)
Values:
- 'ON': Sorts intermediate index results in TempDB.
- 'OFF': Sorts intermediate index results in user database's log file.

@verboseMode (optional)
Values:
0: It does not output additional information on the index reorganization/rebuild process.
1: It outputs additional information on the index reorganization/rebuild process.

Also, please note the following conditions:

  • You must be a SysAdmin in order to execute the script.
  • The script supports only SQL Server 2005 or later.
  • If you execute this script in a SQL Server 2005 instance or later, any databases with compatibility level 2000 (80) or earlier will be automatically excluded from the index reorganization/rebuild process.

The script is provided below:

  
----
-- Script that reorganizes or rebuilds all indexes having an average fragmentation 
-- percentage above a given threshold. It also works in the case
-- where Availability Groups are enabled as it determines if the
-- relevant databases are the primary replicas.
--
-- This script supports only SQL Server 2005 or later.
-- Also, if you execute this script in a SQL Server 2005 instance 
-- or later, any databases with compatibility level 2000 (80) or earlier
-- will be automatically excluded from the index reorganization/rebuild process.
----

--Initial check - You must be SysAdmin
DECLARE @isSysAdmin INT
SET @isSysAdmin=(SELECT IS_SRVROLEMEMBER ('sysadmin'));

--Initial check - You must be using SQL Server 2005 or later
DECLARE @SQLServerVersion INT
SET @SQLServerVersion=(SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)))-1) AS INT));


IF @isSysAdmin=1 AND @SQLServerVersion >= 9
BEGIN 

--
-- Variable/parameters Declaration
--
DECLARE @dbname NVARCHAR(128);
DECLARE @ReorganizeOrRebuildCommand NVARCHAR(MAX);
DECLARE @dbid INT;
DECLARE @indexFillFactor VARCHAR(5); 
DECLARE @fragmentationThreshold VARCHAR(10);
DECLARE @indexStatisticsScanningMode VARCHAR(20);
DECLARE @verboseMode BIT;
DECLARE @reportOnly BIT;
DECLARE @sortInTempdb VARCHAR(3);
DECLARE @isHadrEnabled BIT;
DECLARE @databaseToCheck VARCHAR(250)
DECLARE @dynamic_command NVARCHAR(1024);
DECLARE @dynamic_command_get_tables NVARCHAR(MAX);

--Initializations - Do not change
SET @databaseToCheck=NULL;
SET @dynamic_command = NULL;
SET @dynamic_command_get_tables = NULL;
SET @isHadrEnabled=0;

SET NOCOUNT ON;

---------------------------------------------------------
--Set Parameter Values: You can change these (optional) -
--Note: The script has default parameters set   -
---------------------------------------------------------
--if set to 1: it will just generate a report with the index reorganization/rebuild statements
--if set to 0: it will reorganize or rebuild the fragmented indexes
SET @reportOnly = 0;

--optional: if not set (NULL), it will scann all databases
--If name is set (i.e. 'testDB') it will just scan the given database
SET @databaseToCheck = NULL;

--maintains only the indexes that have average fragmentation percentage equal or higher from the given value
SET @fragmentationThreshold = 15; 

--fill factor - the percentage of the data page to be filled up with index data
SET @indexFillFactor = 90; 

--sets the scanning mode for index statistics 
--available values: 'DEFAULT', NULL, 'LIMITED', 'SAMPLED', or 'DETAILED'
SET @indexStatisticsScanningMode='SAMPLED';

--if set to ON: sorts intermediate index results in TempDB 
--if set to OFF: sorts intermediate index results in user database's log file
SET @sortInTempdb='ON'; 

--if set to 0: Does not output additional information about the index reorganization/rebuild process
--if set to 0: Outputs additional information about the index reorganization/rebuild process
SET @verboseMode = 0; 
------------------------------
--End Parameter Values Setup -
------------------------------

-- check if given database exists and if compatibility level >= SQL 2005 (90)
IF @verboseMode=1
 PRINT 'Checking if database '+@databaseToCheck+' exists and if compatibility level equals or greater 2005 (90)';

 -- if given database does not exist, raise error with severity 20
 -- in order to terminate script's execution
IF @databaseToCheck IS NOT NULL
BEGIN
 DECLARE @checkResult INT
 SET @checkResult=(SELECT COUNT(*) FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck));
 IF @checkResult<1
  RAISERROR('Error executing index reorganization/rebuild script: Database does not exist' , 20, 1) WITH LOG;

 DECLARE @checkResult2 INT
 SET @checkResult=(SELECT [compatibility_level] FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck));
 IF @checkResult<90
  RAISERROR('Error executing index reorganization/rebuild script: Only databases with SQL Server 2005 or later compatibility level are supported' , 20, 1) WITH LOG;  
END

IF @verboseMode=1
 PRINT 'Initial checks completed with no errors.';

-- Temporary table for storing index fragmentation details
IF OBJECT_ID('tempdb..#tmpFragmentedIndexes') IS NULL
BEGIN
CREATE TABLE #tmpFragmentedIndexes
    (
      [dbName] sysname,
      [tableName] sysname,
   [schemaName] sysname,
      [indexName] sysname,
      [databaseID] SMALLINT ,
      [objectID] INT ,
      [indexID] INT ,
      [AvgFragmentationPercentage] FLOAT,
   [reorganizationOrRebuildCommand] NVARCHAR(MAX)
    );
END 

-- Initialize temporary table
DELETE FROM #tmpFragmentedIndexes;

-- Validate parameters/set defaults
IF @sortInTempdb NOT IN ('ON','OFF')
SET @sortInTempdb='ON';

-- Check if instance has AlwaysOn AGs enabled
SET @isHadrEnabled=CAST((SELECT ISNULL(SERVERPROPERTY('IsHadrEnabled'),0)) AS BIT);

-- if database not specified scan all databases
IF @databaseToCheck IS NULL
BEGIN
DECLARE dbNames_cursor CURSOR
FOR
    SELECT  s.[name] AS dbName ,
            s.database_id
    FROM    master.sys.databases s            
    WHERE   s.state_desc = 'ONLINE'
            AND s.is_read_only != 1            
            AND s.[name] NOT IN ( 'master', 'model', 'tempdb' )
   AND s.[compatibility_level]>=90
    ORDER BY s.database_id;    
END 
ELSE
-- if database specified, scan only that database
BEGIN
DECLARE dbNames_cursor CURSOR 
FOR
    SELECT  s.[name] AS dbName ,
            s.database_id
    FROM    master.sys.databases s            
    WHERE   s.state_desc = 'ONLINE'
            AND s.is_read_only != 1                        
   AND s.[name]=RTRIM(@databaseToCheck)    
END 

-- if Always On Availability Groups are enabled, check for primary databases
-- (thus exclude secondary databases)
IF @isHadrEnabled=1
BEGIN

DEALLOCATE dbNames_cursor;

-- if database not specified scan all databases
IF @databaseToCheck IS NULL
BEGIN
 DECLARE dbNames_cursor CURSOR
 FOR
  SELECT  s.[name] AS dbName ,
    s.database_id
  FROM    master.sys.databases s
    LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id
  WHERE   s.state_desc = 'ONLINE'
    AND s.is_read_only != 1
    AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY'
    AND s.[name] NOT IN ( 'master', 'model', 'tempdb' )
    AND s.[compatibility_level]>=90 
  ORDER BY s.database_id;    
END
ELSE
-- if database specified, scan only that database
BEGIN
 DECLARE dbNames_cursor CURSOR
 FOR
  SELECT  s.[name] AS dbName ,
    s.database_id
  FROM    master.sys.databases s
    LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id
  WHERE   s.state_desc = 'ONLINE'
    AND s.is_read_only != 1
    AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY'    
    AND s.[name]=RTRIM(@databaseToCheck);  
END 
END 


--
-- For each database included in the cursor, 
-- gather all tables that have indexes with 
-- average fragmentation percentage equal or above @fragmentationThreshold
--
OPEN dbNames_cursor;
FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid;
WHILE @@fetch_status = 0
    BEGIN   
 
 --If verbose mode is enabled, print logs
        IF @verboseMode = 1
            BEGIN
    PRINT ''
                PRINT 'Gathering index fragmentation statistics for database: ['+ @dbname + '] with id: ' + CAST(@dbid AS VARCHAR(10));    
            END;
                   
        SET @dynamic_command_get_tables = N'
 USE [' + @dbname+ N'];
 INSERT INTO #tmpFragmentedIndexes (
  [dbName],
  [tableName],
  [schemaName],
  [indexName],
  [databaseID],
  [objectID],
  [indexID],
  [AvgFragmentationPercentage],
  [reorganizationOrRebuildCommand]  
  )
  SELECT
     DB_NAME() as [dbName], 
     tbl.name as [tableName],
     SCHEMA_NAME (tbl.schema_id) as schemaName, 
     idx.Name as [indexName], 
     pst.database_id as [databaseID], 
     pst.object_id as [objectID], 
     pst.index_id as [indexID], 
     pst.avg_fragmentation_in_percent as [AvgFragmentationPercentage],
     CASE WHEN pst.avg_fragmentation_in_percent > 30 THEN 
     ''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REBUILD WITH (FILLFACTOR = '+@indexFillFactor+', SORT_IN_TEMPDB = '+@sortInTempdb+', STATISTICS_NORECOMPUTE = OFF);''
     WHEN pst.avg_fragmentation_in_percent > 5 AND pst.avg_fragmentation_in_percent <= 30 THEN 
     ''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REORGANIZE;''     
     ELSE
     NULL
     END
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , '''+@indexStatisticsScanningMode+''') as pst
   INNER JOIN sys.tables as tbl ON pst.object_id = tbl.object_id
   INNER JOIN sys.indexes idx ON pst.object_id = idx.object_id AND pst.index_id = idx.index_id
  WHERE pst.index_id != 0  
   AND pst.alloc_unit_type_desc IN ( N''IN_ROW_DATA'', N''ROW_OVERFLOW_DATA'')
   AND pst.avg_fragmentation_in_percent >= '+ @fragmentationThreshold + '';
        
  -- if verbose  mode is enabled, print logs    
  IF @verboseMode=1
   BEGIN
    PRINT 'Index fragmentation statistics script: ';    
    PRINT @dynamic_command_get_tables;
  END

  -- gather index fragmentation statistics
        EXEC (@dynamic_command_get_tables);
       
     -- bring next record from the cursor
        FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid;
    END;

CLOSE dbNames_cursor;
DEALLOCATE dbNames_cursor;

------------------------------------------------------------

-- if 'report only' mode is enabled
IF @reportOnly=1
BEGIN 
 SELECT  dbName ,
            tableName ,
            schemaName ,
            indexName ,            
            AvgFragmentationPercentage ,
            reorganizationOrRebuildCommand
 FROM    #tmpFragmentedIndexes
 ORDER BY AvgFragmentationPercentage DESC;
END
ELSE 
-- if 'report only' mode is disabled, then execute 
-- index reorganize/rebuild statements
BEGIN 
 DECLARE reorganizeOrRebuildCommands_cursor CURSOR
 FOR
    SELECT  reorganizationOrRebuildCommand
  FROM #tmpFragmentedIndexes
  WHERE reorganizationOrRebuildCommand IS NOT NULL
  ORDER BY AvgFragmentationPercentage DESC;

 OPEN reorganizeOrRebuildCommands_cursor;
 FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand;
 WHILE @@fetch_status = 0
  BEGIN   
         
   IF @verboseMode = 1
   BEGIN
     PRINT ''
     PRINT 'Executing script:'     
     PRINT @ReorganizeOrRebuildCommand
   END
          
   EXEC (@ReorganizeOrRebuildCommand);          
   FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand;
  END;

 CLOSE reorganizeOrRebuildCommands_cursor;
 DEALLOCATE reorganizeOrRebuildCommands_cursor;

 PRINT ''
 PRINT 'All fragmented indexes have been reorganized/rebuilt.'
 PRINT ''
END
END 
ELSE
BEGIN
 PRINT '';
 PRINT 'Error: You need to be SysAdmin and use SQL Server 2005 or later in order to use this script.';
 PRINT '';
END
--End of Script

Example Use of SQL Server Index Rebuild and Reorganize Script

Report Only

Let's see an example of using the script with @reportOnly=1 and with @fragmentationThreshold=15 for the sample database 'AdventureWorks2014'. These settings were changed and the script was run.

The script generated a report with 39 fragmented indexes.

A total of 32 indexes needed to be rebuilt:

Sample Index Maintenance Operation - Report Only Part1

And a total of 7 indexes needed to be reorganized:

Sample Index Maintenance Operation - Report Only Part2

Actual Rebuild and Reorganize Indexes

Now let's run the same example, but this time using the script with @reportOnly=0, @fragmentationThreshold=15 and database is set to 'AdventureWorks2014' as before.

Sample Index Maintenance Operation - Execution Outcome

As you can see, all indexes were reorganized / rebuilt.

Summary

The above script is very powerful and can help you maintain your indexes not only in standalone or clustered indexes of SQL Server, but also in AlwaysOn-Enabled instances that use Availability Groups.

Reorganizing and rebuilding indexes is not always the "magic" solution. It can sure help performance, but you need to maintain the health of your SQL Server instances in every aspect. You need to keep statistics updated, take into consideration how the storage system you use is organized and operates, set the proper parameters for your database settings and much more.

Nevertheless, index reorganization and specially rebuilds is one of the top choices for many DBAs as it is a fast way to fix (even temporarily sometimes) performance issues. This tip can help you through this process by simplifying the entire procedure.

Next Steps

Here are some additional thoughts:

  • This script is a starting point. This can be run as is or you can add to it to meet your specific needs.
  • To address index fragmentation for more complex needs, consider a table driven solution to meet the unique index needs of each table.
  • Study the parameters the script offers and understand your options when addressing index fragmentation.
  • Run first in a test / dev environment and once comfortable with the code think about converting it to a stored procedure or how you would use this in your environment.
  • By using parts of the script, you can create even more sophisticated solutions for index fragmentation.

Also review the following tips and resources:



Last Update:


signup button

next tip button



About the author
MSSQLTips author Artemakis Artemiou Artemakis Artemiou is a Senior SQL Server Architect, Software Developer, and a Microsoft Data Platform MVP (SQL Server).

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, May 27, 2017 - 7:14:15 AM - Artemakis Artemiou Back To Top

Hi Animesh,

 

Thank you for your kind words.

 

About point 1, this check is not really necessary because the script finds these objects (indexes) using system tables and dynamic management views prior to constructing the reorganization/rebuild T-SQL statements. By the time the script finds them, it means that they exist.

 

Regarding point 2, note that whenever the T-SQL script is executed, if there is any error, is reported by SQL Server Database Engine. If, however, you want to have more control over each index reorganization/rebuild statement, you can set the script's parameter "SET @reportOnly = 1;" and then you will be able to control via SSMS the execution of each one of those T-SQL statements.

 


Wednesday, May 24, 2017 - 9:59:00 AM - Animesh Back To Top

 This is really helpful. Thanks!

We need to add two thing on this, please help me out how to do:

1.We want to add a check just before the execution of the ReorganizeOrRebuildCommand whether the object exsists or not

2.If there is any error during the execution of any index ReorganizeOrRebuildCommand, we need the error to be logged in the output and go forward with the next index.

 


Monday, May 15, 2017 - 12:28:37 PM - Artemakis Artemiou Back To Top

Hi karthik,

 

Thank you for your comment.

 

You are correct. The script does not support columnstore indexes.

 

The reason is that the ALTER INDEX...REBUILD statement for columnstore indexes does not accept the WITH(FILLFACTOR = ..., SORT_IN_TEMPDB = ..., STATISTICS_NORECOMPUTE = ...) parameters.

 

There is however a workaround:

 

You can set @reportOnly = 1;

 

and then just for the columnstore indexes you can remove from the "reorganizationOrRebuildCommand" REBUILD T-SQL statements the "WITH" keyword along with its parameters. 

 

For example, for columnstore index "idx_tblColumnStore" you can replace the below index rebuild statement:

 

ALTER INDEX [idx_tblColumnStore] ON [dbo].[tblColumnStore] REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF);

 

with the below:

 

ALTER INDEX [idx_tblColumnStore] ON [columnstore].[dbo].[tblColumnStore] REBUILD;

 

 

Regards,

Artemakis

 

P.S.: The ALTER INDEX...REORGANIZE statements work for columnstore indexes as well.

 


Friday, May 12, 2017 - 11:27:38 PM - karthik Back To Top

The code doesnt work for Columnstored Index !!!

 


Monday, January 30, 2017 - 1:24:55 PM - Artemakis Artemiou Back To Top

 

Thank you for your kind words Lawrence!

 

Cheers,

Artemakis


Monday, January 30, 2017 - 1:22:49 PM - Artemakis Artemiou Back To Top

Hi Stacy,

Thank you very much for your kind words!

I'm really glad I helped!

 

Kind regards,

Artemakis Artemiou

 


Saturday, January 28, 2017 - 7:53:03 AM - Stacy Back To Top

Thank you so much! This worked perfectly!

 


Saturday, January 14, 2017 - 8:04:41 AM - Lawrence Back To Top

 Very nice script!

 


Saturday, January 14, 2017 - 3:46:05 AM - Artemakis Artemiou Back To Top

Hi Jeffrey,

Thank you for your comment.

If you just copy-paste the script it will work with the default settings. That means that it will rebuild the fragmented indexes for all databases in the SQL instance.

 

If you want the script to scan all databases then you can use the below (this is the default setting):

SET @databaseToCheck =  NULL;

 

If you need to scan a single database, for example database 'TestDB1', then you can use the below:

SET @databaseToCheck =  'TestDB1';

 

Hope this helps.

 


Friday, January 13, 2017 - 11:28:07 AM - Jeffrey Back To Top

We are having trouble with the script executing successfully and have decided to split out one large database and have it run alone in a job and all the other databases run together.  In the first job I have the option

--optional: if not set (NULL), it will scann all databases

--If name is set (i.e. 'testDB') it will just scan the given database

SET @databaseToCheck =  (select name from sys.databases where name <>'databasename');

And in the other job I have done the following

--optional: if not set (NULL), it will scann all databases

--If name is set (i.e. 'testDB') it will just scan the given database

SET @databaseToCheck =  ‘databasename’

Do you believe this will work.  your help is apprecited.

The failure is that it stops when processing the  

 

 


Thursday, September 29, 2016 - 10:58:39 AM - Artemakis Artemiou Back To Top

Hi Michail,

 

Thank you for your comment. It is an excellent question. This is a very common phenomenon, especially in the case of small indexes.

 

Below I am quoting the relevant MSDN article which can be found at: https://msdn.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

 

"In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index."

 

As a sidenote, if we are talking about tables that are not partitioned, do not try to update the index statistics after the rebuild, because in the case where an index rebuild command is executed, it also updates the index statistics.

 

Kind regards,

Artemakis


Thursday, September 29, 2016 - 4:28:38 AM - Michail Back To Top

 After the script has a lot of indexes are not defragmented. If you run the script that some indices are corrected several times, but many still remain defragmented.

 

 


Learn more about SQL Server tools