Differences between rebuild and re-org index
Syntax
Alter Index Index_Name on Table_Name REORGANIZE
Alter Index Index_Name on Table_Name REBUILD
Rebuild Index drops the existing index and re-creates the index from the scratch.
Rebuild index can be executed online or offline.
Rebuild Index uses more CPU and it locks the database resources.
In rebuild index we can specify index options like fill factor or index padding.
Rebuild locks the table for whole operation except Online = ON (Enterprise Edition)
Rebuild index automatically updates indexed column statistics.
By default Rebuild index is offline process, it will cause some blocking, we can use online=on to build index online and reduce blocking during the process.
Reorganize index physical organize the leaf nodes of the index.
Reorganize always executed online, (It wont block applications) In Reorg SQL Server read through all leaf level pages of an index and reorders them.
Reorg index uses less CPU and it doesn’t lock the database resources.
In reorg index we cannot specify and index options.
Reorg doesn’t lock the table.
Reorganize index doesn’t update statistics (After reorg we need to manually run update stats on the table).
Recommendation – Rebuild index when fragmentation is greater than 30% and Reorganize index if fragmentation is between 10 to 30%.
Query to find fragmentation percent
SELECT Object_name(object_id) as Tablename,s.name as Indexname
,index_type_desc
,avg_fragmentation_in_percent
,page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') d
join sysindexes s on d.object_id = s.id
and d.index_id = s.indid
and s.name ='Index_Name'
No comments: