Sql server ReIndex Or ReOrganize

What is the difference between Index REBUILD and REORGANIZE and when exactly should you use it

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:

Hide   Copy Code

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:

Hide   Copy Code

USE 
GO

ALTER INDEX ALL ON  REBUILD
GO

In order to build only a specific index:

Hide   Copy Code

USE 
GO

ALTER INDEX  ON  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:

Hide   Copy Code

USE 
GO

ALTER INDEX ALL ON  REORGANIZE
GO

In order to reorganize only a specific index:

Hide   Copy Code

USE 
GO

ALTER INDEX  ON  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):

Hide   Copy Code

USE 
GO

ALTER TABLE  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.

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)License