Couple of days back there was an interesting statement (or rather a question) was brought up by one of the colleagues in the company. Ultimately the initial stement left us with one simple question, which is the difference between Index REBUILD and REORGANIZE and when should be exactly use 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 to 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 on 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 is it. (clustered, non-clustered, heap etc…)
As per the guidlines 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 useing the following syntax:
In order to build all the indexes on a specific table:
USE <Database_Name>
GOALTER INDEX ALL ON <Table_Name> REBUILD
GO
In order to build only a specific index:
USE <Database_Name>
GOALTER 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 syntx:
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 (Eg: 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
How ever REBUILD or REORGANIZE will not have an effect on the HEAP fragmentation. In order to remove the heap fragmentation you can use the followng syntax (*** NOT THE BEST PRACTICE):
USE <Database_Name>
GOALTER TABLE <Table_Name> REBUILD
GO
** Eventhough 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 practise would be to create a clustered index on the table to remove the HEAP fragmentation. You can find more details on this on the following blog post by Paul. S Randal which he had illustrated nicely.
![image_thumb[2]](https://lh3.googleusercontent.com/-MwvZpo158Gw/Vp91FuVxB_I/AAAAAAAADmc/kyiCsU_WTUc/s1600-h/image_thumb%25255B2%25255D%25255B9%25255D.png "image_thumb[2]")
![image_thumb[4]](https://lh3.googleusercontent.com/-FTLVn5nopmo/Vp91G8Gfc4I/AAAAAAAADms/0Dt4kmpQ61A/s1600-h/image_thumb%25255B4%25255D%25255B6%25255D.png "image_thumb[4]")