Web Analytics Made Easy - Statcounter

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

ALTER INDEX ALL 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

ALTER INDEX ALL 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

Fragmentation

DMCA.com


Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

31 thought on “Rebuild And Reorganization of Indexes”
  1. … [Trackback]

    […] Find More on that Topic: techmixing.com/2012/12/rebuild-and-reorganization-of-indexes.html […]

  2. … [Trackback]

    […] Here you can find 80010 more Info on that Topic: techmixing.com/2012/12/rebuild-and-reorganization-of-indexes.html […]

  3. … [Trackback]

    […] There you will find 45201 more Info on that Topic: techmixing.com/2012/12/rebuild-and-reorganization-of-indexes.html […]

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading