Best Way to Rebuild and Reorganize Index in SQL Server

Summary: This writes up expounds the methodical procedures to reorganize and rebuild any fragmented index in MS SQL Server. The entire techniques are estranged into various subdivisions to make this operation uncomplicated for users. Through stated procedures, users possibly will accomplish the task without any data loss. Towards the last part of this article, it also carries a professional method of rebuilding index in SQL Server.

What is Index Fragmentation in SQL Server

The SQL Server index is analogous to the index of any book. This index assists to get a quick idea about contained data, but instead of navigating any book, it is a catalog of the SQL Server database.

Whenever any search operation becomes performed in SQL, the SQL Server searches the value in its index and after that locates that entire row of data. Therefore, SQL Server does not perform a full table scanning process for searching any data and present us the required data through its indexes.

The SQL Server automatically sustains its indexes after executing any operation in it as INSERT, UPDATE, MERGE and DELETE. When the logical order of pages in an index does not match with its physical data order, the index fragmentation happens. The deeply fragmented index mortifies the performance of MS SQL Server and it may the cause of slow responding OS.

The fragmentation also impinges on the executed queries and range scanning process. A lot of unused space may increase the number of pages in index.
The index fragmentation can be monitored only via rebuilding or re-creating that index. This process eliminates the fragmentation and repossesses the storage space by compacting the size of pages.

In this defragmentation process, we set the particular or existing fill factor, and then, it maintains the index rows in adjacent pages. Rebuilding any index utilizes only some negligible system resources. reorganizing defragments the leaf level of the clustered index and non-clustered indexes on tables. Rebuilding indexes also compact the number of index pages. The index compaction is based on the presented fill factor’s value.

How to Detect Index Fragmentation in SQL Server

The information regarding internal index fragmentation, can be easily detected by the dynamic management view (DMV) that is sys.dm_db_index_physical_stats. The DMV defragments the index information and returns it to its exact size. Via DMV, users can acquire the information regarding to the amount of fragmentation in rows on a specific data page. This can detect that the data reorganization is essential or not.

avg_fragmentation_in_percent: average percent of logical fragmentation is inaccurate in the index

fragment_count: number of fragments in the index

avg_fragment_size_in_pages: average number of pages in a single fragment in one index

Investigating Detected Results In SQL

Once the fragmentation in the index has been perceived then, its establishment is the next step. There is a usually a conventional resolution for each level of fragmentation based on the proportion of fragmentation:

If fragmentation < 10% – no need of defragmentation. It is an acceptable amount and does not influence the working of MS SQL Server

If fragmentation ≤ 10-30% –Index restructuring is required

If fragmentation ≥ 30% – Index rebuilding is must

Reorganizing and Rebuilding Index in MS SQL Server

Reconstruction of an index is necessary when the index fragmentation has attains to a significant percentage. In this section, we will rebuild index via SQL Server Management Studio and Transact-SQL.

Reorganizing and Rebuilding Index via MS SQL Server Management Studio

To rebuild any fragmented index of SQL Server, there may be two possible conditions i.e. rebuilding a single index another rebuilding all indexes of a table. Here are the total solutions of how to reorganize and rebuild any index via SQL Server Management Studio:

Case 1: Reorganizing a single Index

• Click on object explorer arrow; select the database that holds the table to which you need to reorganize and rebuild
• Select the Tables folder
• Click the index and expend Indexes folder
• Hit a right-click on the index you need to reorganize and select reorganize from expended menu list
• Verify the index from Indexes to be reorganized and click on Ok button
• Now, click on Compact large object column data
• Check the box to identify that entire pages that contain large object data (LOB) be also compacted
• Click on Ok button

Case 2: Reorganizing all Indexes of a Table

• Click on drop-down arrow of database that contain the table in, which you need to reorganize the indexes
• Now, click on Tables to expend its menu list
• Hit a right-click on Indexes and choose reorganize All
• In reorganize Indexes wizard, inspect the indexes from Indexes to be reorganize
• If you need to remove any index then, select that index and hit the Delete button
• Choose Compact large object column data and verify that all pages contain large object (LOB) data are compacted or not
• Then, click on Ok button

Rebuilding the Index in MS SQL Server

• Select the database and click on its object explorer arrow
• Click on table that has the indexes you need to rebuild
• Choose Tables folder and expand it
• After that, click on desired index you need to reorganize and open it
• Now, right-click on index and select reorganize option
• Rebuild Indexes box is opened now, check the input index via Indexes to be rebuilt option
• Click on Ok button
• For specifying all large object data (LOB) pages, click on Compact large object column data
• Hit a click on Ok button

Reorganizing and Rebuilding Index via T-SQL

In this section, we will discuss the method of recognizing and rebuilding any single or multiple indexes of any table using Transact-SQL.

Case 1: Reorganizing a Single Defragmented Index

• • Click on Object Explorer
  • From standard bar, click on New Query option
  • Now, copy the mentioned command and paste it into a query window

• After that, click on Execute

Case 2: Reorganizing All defragmented Indexes

• • Select the database and explore it
  • Connect it with Database Engine
  • Click on New Query option that is situated in Standard bar
  • Copy the down mentioned commands and paste it into query pane

• Click on Execute button

Rebuilding a Single Defragmented Index

• • Click on Object Explorer and connect it with Database Engine
  • Select New Query, from standard bar options
  • Copy the mentioned commands and paste them into query Window

Rebuilding all defragmented Indexes

• • Connect the Object Explorer with Database Engine
  • Click on New Query
  • Now, copy the down mentioned queries and paste them into query pane

• Executing these commands will repair the entire indexes that are associated with a table

Automatic index Rebuilding and Statistics Administration

Manual methods are risky as well as time taking too. Users must need the technical knowledge for rebuilding indexes in MS SQL Server. Therefore, for preventing users via these cons of manual methods, we represent a powerful solution i.e. SQL Server Index Repair Tool . It automatically manages the index defragmentation along with its statistics updates intended for single or additional databases. This is the method of automatically rebuilding or reorganizing any index as per its fragmentation level. This is capable to recover the data that is affected by wallet ransomware attack.

Conclusion

After understanding the significance of fragmentation in indexes of MS SQL Server, it becomes essential to recognize and rebuild the indexes. For rebuilding the fragmented indexes, we have discussed various methods of rebuilding index in SQL Server. Users can accomplish this task in two different manual and automatic manners as mentioned above. The SQL Server Recovery tool is one of the best solutions to execute the mentioned task in a hassle-free manner. Now, users can opt any of the suitable technique in order to do this.