What are the effects of Index Rebuilding and Reorganizing on clustered and non-clustered indexes. How does these 2 operations affect the logical storage and physical storage of data in Hard disk?

  • 1
    Hi @Jigar - welcome to Database Administrators ! Please take the tour and read the help center for details about how to use the site, and how to ask good questions. As it is, this question is likely too broad for this site. – Max Vernon Jun 3 '16 at 15:42

The MSDN page says this about reorganizing and rebuilding indexes:

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. 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.

Doe that cover your question?

  • 1
    Only thing I would add to this is with regards to ONLINE rebuilds. New index is rebuilt alongside old index, so the index effectively doubles in size for the rebuilding process. Thats my understanding anyway. – Molenpad Jun 3 '16 at 20:15
  • hi Max, actually i want to know the pros and cons of the above 2 mentioned operations on indexes. Since index are of 2 types, the concerned effects must be varying. So i am looking for a little help in better understanding the concepts. – Jigar Shah Jun 6 '16 at 11:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.