1 / 1

DIFFERENCE BETWEEN REBUILDING AND REORGANIZING INDEXES

You can remedy index fragmentation by reorganizing or rebuilding an index. It is prudent to know the differences between the two:

Download Presentation

DIFFERENCE BETWEEN REBUILDING AND REORGANIZING INDEXES

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. DIFFERENCE BETWEEN REBUILDING AND REORGANIZING INDEXES You can remedy index fragmentation by reorganizing or rebuilding an index. It is prudent to know the differences between the two: Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. Rebuilding of indexes is mainly an offline operation (will not skip pages), but reorganizing an index is mainly an online operation (may skip pages) Stopping of rebuilding will cause a rollback and loss of work. Stopping of reorganizing will not cause any loss of work Rebuilding an index updates the index statistics (but not column statistics). However, reorganizing neither updates index statistics nor updates column statistics (which is why you should always run sp_updatestats after rebuilding/reorganizing your indexes) Rebuilding requires additional free space with the data files, reorganizing does not Rebuilding can make use of parallelism according to your MAXDOP setting. Reorganizing cannot make use of parallelism Rebuilding usually runs faster for indexes with high level of logical fragmentation. However, reorganizing is usually faster for indexes with low levels of fragmentation Rebuilding usually runs much faster than reorganizing on very large indexes To read more, please visit Satish Kartan's blog where he has shared much important information! Also read: SAN VS DAS –WHAT’S GOOD FOR SQLby Satish Kartan

More Related