Rebuild And Reorganization of Indexes

Rebuild and  Reorganization of Indexes

SQL Server has the ability of maintaining the indexes whenever we makes changes (update, Insert, Delete) in the tables. Over a period of time, the may causes the fragmentation on the table in which  the logical ordering based on the key value pairs does not match with the physical ordering inside the data files. This causes the degradation of the performance of the SQL Query.To solve this problem of fragmentation, we use rebuilding or reorganization of the indexes.In case of Rebuilding, it drop the particular index and again recreate it.It removes fragmentation, reclaims the disk space by compacting the pages based on the specified or existing fill factor setting, and again reorders the index rows in those contiguous pages. We can rebuild all the indexes of the table within a single transaction by specifying the ALL with it.

ALTER INDEX [Indexname] ON [tablename] REBUILD


In case of Reorganization, it defragments the leaf level nodes of indexes by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes.  It uses minimal system resources and also compact the index pages

ALTER INDEX [Indexname] ON [tablename] REORGANIZE


Rebuild should be used when the fragmentation index is greater than 30% and reorganization option should be used when the fragmentation index is between 5% and 30%. Rebuilding of an Index can be done online or offline. But to achieve the availability of the index, rebuilding should be done online. Reorganization can be done online.

Related Article


About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Indexes, SQL Server and tagged , . Bookmark the permalink.

31 Responses to Rebuild And Reorganization of Indexes

  1. Pingback: buy anabol

  2. Pingback: roulette

  3. Pingback: Restaurants in South County

  4. Pingback: keto

  5. Pingback: tree removal nashville tn

  6. Pingback: 안전토토사이트

  7. Pingback: w88

  8. Pingback: ศัลยกรรมเกาหลี

  9. Pingback: google analytics wordpress

  10. Pingback: ww88

  11. Pingback: 우리카지노

  12. Pingback: didi promo code

  13. Pingback: Jerome Powell Speech Today Stock Market Economy Gold Silver

  14. Pingback: 바카라사이트

  15. Pingback: เหนียวหนี้ห

  16. Pingback:

  17. Pingback: laser hair removal on the upper lip

  18. Pingback: לטיול יצאנו

  19. Pingback: cbd oil tastes like

  20. Pingback: Dream Market Laden

  21. Pingback: marketing research 2016

  22. Pingback: Empire Market

  23. Pingback: xembongdatructuyen online

  24. Pingback: porno

  25. Pingback: honda qq

  26. Pingback:

  27. Pingback: granice

  28. Pingback: กังนัม คลินิก

  29. Pingback:

  30. Pingback: poker 99

  31. Pingback: dang ky tai khoan m88

Leave a Reply