Reorganize vs Rebuild Indexes

DBAs frequently have to choose between rebuilding the indexes and reorganizing them.

Depending on how fragmented the index is, one must make a choice. Reorganizing will be a quicker, more effective solution if fragmentation is low. Rebuilding is the speedier, more effective, and more appropriate course of action if fragmentation levels are high.

Reorganizing the index:
As part of the index reorganization process, SQL Server cleans up each existing index. Index rearrangement is a pure cleanup operation that keeps the system in its current state by not locking out the tables and views that are affected. Index reorganization is a straightforward technique that will less successfully address fragmentation because a cleansed index will never be able to keep up with a newly created one. The impacted indexed table is not locked while it is in use, so reorganizing the index is substantially more effective.

Rebuilding the index:
The index is completely rebuilt during the index rebuild, starting from scratch with a brand-new structure that is free of all accumulated fragments and blank space pages. When a table is rebuilt, the process locks it for the duration of the rebuild, which could cause lengthy downtimes that are unacceptable in some situations. This indicates that rebuilding an index is the best option, although it may result in lengthy locks on the indexed tables that are affected.

When to use:
Reorganizing the index is a suitable, quick, and effective option when the fragmentation level is lower. Rebuild is the best option when fragmentation levels are high. Without having proper analysis if one opted to rebuild the indexes, it will cause a lot of unnecessary I/O and transaction log activity.

Run the following query against the system tables to check the fragmentation levels.

SELECT
OBJECT_NAME(A.[object_id]) as ‘TableName’,
B.[name] as ‘IndexName’,
A.[index_id],
A.[page_count],
A.[index_type_desc],
A.[avg_fragmentation_in_percent],
A.[fragment_count]
FROM
sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,’LIMITED’) A
INNER JOIN sys.indexes B ON A.[object_id] = B.[object_id] AND A.index_id = B.index_id

Examine the “avg_fragmentation_in_percent” number and take into account the broad recommendations for what should be done at what value.

Better to do nothing if the fragmentation value is less than 5%.
Reorganizing the index is advised if the fragmentation value is greater than 5 percent to 30.
Rebuilding the index is advised if the fragmentation value is greater than 30%.

If the database is huge, better to consider the following –

Reorganize the indexes if the value is greater than 10% AND less than 30%.
Otherwise, rebuild the indexes if it is higher than 30%.

The following are examples for reorganize and rebuild the indexes.

— Reorganize a specific indexes Employee table.
ALTER INDEX IX_Employee_EmployeeProfileID
ON Employee
REORGANIZE ;
GO

— Reorganize all indexes on the Employee table.
ALTER INDEX ALL ON Employee
REORGANIZE ;
GO

— Rebuild a specific indexes Employee table.
ALTER INDEX IX_Employee_EmployeeProfileID ON Employee
REBUILD;
GO

— Rebuild all indexes on the Employee table.
ALTER INDEX ALL ON Employee
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO