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 -

• T-SQL to find Fragmented Indexes
• What is the best value for Fill Factor in SQL Server?
• Rebuild all the indexes of a table & Indexed views in a Database
• Rebuild all the Indexes of a SQL Database in one go

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 INDEXDEFRAG 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.