Reorganize Index in SQL server

Reorganizing an index in SQL Server is a lighter operation compared to rebuilding it. When we reorganize an index, SQL Server defragments the index leaf level pages and reorders them to match the logical order of the index keys without creating a new index structure. The index reorganization process physically reorganizes the leaf nodes of the index. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, and order of the leaf nodes. Reorganizing also compacts the index pages.

Unlike rebuilding indexes, reorganizing indexes does not require any additional disk space but requires log space. Because reorganizing indexes is a fully logged transaction, the log can potentially grow substantially. Log usage for a reorganize could be way more than the size of the index, which depends on how much reorganizing and compaction is done, as well as the level of fragmentation distributed through the index.

Reorganizing an index does not lock the table, so it can be done online, even on editions that do not support online rebuilds. It is much faster and less resource-intensive than rebuilding but may not fully resolve severe fragmentation.

Reorganize a Specific Index

ALTER INDEX [index_name] ON [table_name] REORGANIZE;

 Reorganize All Indexes on a Table

ALTER INDEX ALL ON [table_name] REORGANIZE;

 Reorganize All Indexes in a Database

DECLARE @TableName NVARCHAR(256); DECLARE @SQL NVARCHAR(max); DECLARE table_cursor CURSOR FOR   SELECT Quotename(Schema_name(schema_id)) + '.' + Quotename(NAME)   FROM   sys.tables; OPEN table_cursor; FETCH next FROM table_cursor INTO @TableName; WHILE @@FETCH_STATUS = 0   BEGIN       SET @SQL = 'ALTER INDEX ALL ON ' + @TableName   + ' REORGANIZE;';       EXEC Sp_executesql    @SQL;       FETCH next FROM table_cursor INTO @TableName;   END;

 Reorganize with LOB_COMPACTION Option

If our index contains large objects (LOBs) such as varchar (max) or varbinary (max), we need to use the LOB_COMPACTION option to compact these objects during reorganization.

ALTER INDEX [index_name] ON [table_name] REORGANIZE WITH (LOB_COMPACTION = ON);