Both rebuild index and reorganize index are used to avoid the index fragmentation. Below are difference between them.
|
Rebuild
index |
Reorganize
index |
|
Rebuild index if
avg_page_space_used_in_percent >=30% |
Reorganize index if avg_page_space_used_in_percent
>5 and <=30% |
|
Index rebuilding involves dropping and recreating an index entirely.
This process rebuilds the index from scratch, organizing the data pages in a
contiguous manner. |
Index reorganizing is a more
lightweight approach that defragments the index by physically reordering the
index pages without dropping and recreating the index itself. |
|
When an index is rebuilt, fragmentation is
eliminated, and index statistics are updated. This can result in better query
performance, especially for heavily fragmented indexes. |
Reorganizing reduces
fragmentation by compacting the pages and the data within them, but it does
not update index statistics. |
|
Index rebuilding requires more system resources and can cause downtime,
particularly on large tables or during peak usage hours. It also resets the
fill factor, impacting future index maintenance. |
Reorganizing is typically faster and
less resource-intensive than rebuilding, making it suitable for routine
maintenance tasks and scenarios where minimal downtime is essential. |
|
Rebuilding is a drastic measure, but it
does its job well. Fragmentation is completely eliminated after it. |
Reorganizing may not
completely eliminate fragmentation in heavily fragmented indexes, and it does
not reset the fill factor. |
