Index REBUILD vs. REORGANIZE in SQL SERVER

Couple of days ago, there was an interesting statement (or rather a question) that was brought up by one of the colleagues in the company. Ultimately, the initial statement left us with one simple question, which is the difference between Index REBUILD and REORGANIZE and when should you be exactly using it.

If you Google the aforementioned, you can find numerous posts/blogs regarding this. Therefore, I will keep things very simple and easier way to understand.

Rebuilding an index or reorganizing is required when index fragmentation has reached a considerable percentage. The fragmentation percentage can be identified using the Dynamic Management View - sys.dm_db_index_physical_stats in SQL Server.

You may get more details on the view at the following link:

• https://msdn.microsoft.com/en-us/library/ms188917.aspx.

You can get a list of fragmented indexes using the following query:

SELECT
    OBJECT_NAME(Stat.object_id)
    ,I.name
    ,Stat.index_type_desc
    ,Stat.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) AS Stat
JOIN sys.indexes AS I
        ON Stat.index_id = I.index_id
        AND Stat.object_id = I.object_id
WHERE
    Stat.avg_fragmentation_in_percent > 30

Executing the above query will give you a list of fragmented indexes which has more than 30% fragmentation. ‘index_type_desc’ will give you a hint what sort of index it is. (clustered, non-clustered, heap, etc.)

As per the guidelines provided by Microsoft, it’s the best practice to Reorganize the index if the fragmentation is less than or equal to 30% (more than 5%) and Rebuild it if it’s more than 30%.

Rebuilding Indexes

• Should perform this if the fragmentation is more than 30%
• Operation can be done online or offline

Index rebuilding can be done using the following syntax:

In order to build all the indexes on a specific table:

USE <Database_Name>
GO

ALTER INDEX ALL ON <Table_Name> REBUILD
GO

In order to build only a specific index:

USE <Database_Name>
GO

ALTER INDEX <Index_Name> ON <Table_Name> REBUILD
GO

Reorganizing Indexes

• Should perform this if the fragmentation is more than 5% but less than or equal to 30%
• Operation is always online

Index reorganizing can be done using the following syntax:

In order to reorganize all the indexes on a specific table:

USE <Database_Name>
GO

ALTER INDEX ALL ON <Table_Name> REORGANIZE
GO

In order to reorganize only a specific index:

USE <Database_Name>
GO

ALTER INDEX <Index_Name> ON <Table_Name> REORGANIZE
GO

Optionally, you can set many attributes during the Rebuild or Re-Organize process (E.g.: FILLFACTOR, SORT_IN_TEMPDB, etc.). Please check on the following link for more details on the REBUILD options:

• https://msdn.microsoft.com/en-us/library/ms188388.aspx

However, REBUILD or REORGANIZE will not have an effect on the HEAP fragmentation. In order to remove the heap fragmentation, you can use the following syntax (*** NOT THE BEST PRACTICE):

USE <Database_Name>
GO

ALTER TABLE <Table_Name> REBUILD
GO

** Even though the aforementioned syntax will remove the HEAP fragmentation, it is considered as bad as creating and dropping a clustered index, which will leave behind lots of fragmentation on non clustered indexes. The best practice would be to create a clustered index on the table to remove the HEAP fragmentation. You can find more details on this on this blog post by Paul. S Randal which he has illustrated nicely.