Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Fragmented Index – Rebuild or Reorganize ?

Introduction

In past, many times I have written on Fragmentation of Indexes and how to Defrag them. You can find some related articles at -

After this, many of my readers have queried me for-

  1. What is the basic difference between ReBuild & ReOrganizing an Index?
  2. When to use what?

So, today I will address these two specific queries and also show a handy TSQL which could be used to remove Index fragmentation.

ReBuilding Index means the Index will be ReBuild from scratch using the same definition of its creation. It is same as using DBCC DBREINDEX. Please note that for new development do no use DBCC DBREINDEX as its deprecated from SQL Server 2005 onwards. It is generally preferred to rebuild OFFLINE. However, rebuild could be done ONLINE by specifying appropriate options.

ReOrganizing Index means the data at the Index leaf will be re-organized and the fragments will be removed to the max possible extent. It is same as using DBCC INDEXDEFRAG. Please note that for new development do no use DBCC DBREINDEX as its deprecated from SQL Server 2005 onwards. ReOrganizing an Index is an ONLINE process.

When to use what – As per Microsoft, the recommendations are as under -

If Avg. Fragmentation < 5%, no action is required

else if 5% < Avg. Fragmentation <= 30%, reorganization is required

else if Avg. Fragmentation > 30%, rebuild is required

So, based on the above recommendations, the below TSQL could be used to serve the purpose -

USE DBName
GO
 
DECLARE @tsql NVARCHAR(MAX)  
DECLARE @fillfactor INT
 
SET @fillfactor = 70 
 
DECLARE @FragmentedIndexs TABLE (IndexID INT,
                                 IndexName VARCHAR(100),
                                 ObjectName VARCHAR(100),
                                 AvgFragmentationInPercent DECIMAL(6,2),
                                 FragmentCount INT,
                                 AvgFragmentSizeInPage DECIMAL(6,2),
                                 IndexDepth INT)
 
--Insert the Details for Fragmented Indexes.
INSERT INTO @FragmentedIndexs
SELECT 
  PS.index_id,
  QUOTENAME(I.name) Name,
  QUOTENAME(DB_NAME()) +'.'+ QUOTENAME(OBJECT_SCHEMA_NAME(I.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(I.[object_id])) ObjectName,
  PS.avg_fragmentation_in_percent,
  PS.fragment_count,
  PS.avg_fragment_size_in_pages,
  PS.index_depth
FROM 
  sys.dm_db_index_physical_stats (DB_ID(), NULL ,NULL, NULL, NULL) AS PS
INNER JOIN sys.indexes AS I 
  ON PS.[object_id]= I.[object_id]
  AND PS.index_id = I.index_id
WHERE
  PS.avg_fragmentation_in_percent > 5  
ORDER BY 
  PS.avg_fragmentation_in_percent DESC
 
--Select the details.
SELECT * FROM @FragmentedIndexs
 
--Prepare the Query to REORGANIZE the Indexes
SET @tsql = ''
 
SELECT @tsql = 
  STUFF(( SELECT DISTINCT 
           ';' + 'ALTER INDEX ' + FI.IndexName + ' ON ' + FI.ObjectName + ' REORGANIZE '
          FROM 
           @FragmentedIndexs FI
          WHERE
            FI.AvgFragmentationInPercent <= 30
          FOR XML PATH('')), 1,1,'')
  
SELECT @tsql
PRINT 'REORGANIZING START'
EXEC sp_executesql @tsql 
PRINT 'REORGANIZING END'
 
--Prepare the Query to REBUILD the Indexes
SET @tsql = ''
 
SELECT @tsql = 
  STUFF(( SELECT DISTINCT 
           ';' + 'ALTER INDEX ' + FI.IndexName + ' ON ' + FI.ObjectName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ') '
          FROM 
           @FragmentedIndexs FI
          WHERE
            FI.AvgFragmentationInPercent > 30
          FOR XML PATH('')), 1,1,'')
  
SELECT @tsql
PRINT 'REBUILD START'
EXEC sp_executesql @tsql
PRINT 'REBUILD END'

Conclusion

To keep the indexes in proper shape, a timely defragmentation is necessary. When to defragment has got no THUMB RULE, it depends on the environment. However, the above script could be scheduled as per ones need to make sure that the indexes gets defragmented when need.

Comments

Posted by realmerl on 26 July 2012

Hi Great article.

I think we all know the rebuilding an inex rebuilds, it,a nd reorganising it basically defraggs it, but the question is, do they end up with the same result? ie a nice clean optimised index? - or woudl the ideal be to not defrag, but to rebuild if enough time available?

Posted by vinaypugalia on 27 July 2012

Hi,

It is not guaranteed that both ReBuild & ReOrganize ends up with same results always. As the former creates fresh new indexes & the later just removes the fragments to the  max possible extent.

The "When to use What" section explains the recommendations and per that it is not like we should always go for rebuild if we have time. For e.g., for small tables of few thousand rows, a small fragment would hardly show any significant performance drop in general cases, so a ReOrganize or ReBuild would not matter much in this case.

Hope, this is helpful.

Posted by vanessa4biz on 16 February 2014

How can this script be used if I am using a fetch statement with a Select from sys.databases? I would like to create a script similar  but I would like to perform this function on all the databases within the instance. Would you create this as a stored procedure within the master folder?

Posted by vinaypugalia on 16 February 2014

Hi,

To accomplish the above functionality for all the DBs in a given instance of SQL Server, you can make the part which is inserting the details in @FragmentedIndexs as DYNAMIC QUERY and insert into #FragmentedIndexs instead inside your Fetch-Loop (which is I believe looping on all the required DBs) and change the T-SQL to then fetch the details from #FragmentedIndexs.

Please note, you will have to make appropriate changes in your T-SQL to make it DYNAMIC.

Thanks!

Leave a Comment

Please register or log in to leave a comment.