How to Reorganize and Rebuild Index in SQL Server Database? An Overview

Summary: When your SQL Server index becomes fragmented, your server avoids using it. It is for sure that fragmentation results in the degradation of query performance and data concurrency. In this article, we’re going to reorganize and rebuild index in SQL Server by executing the defragmentation operations.

Download Now Purchase Now

However, even if SQL Server index defrag operation is necessary, it still can result in the corruption of data files. Downloading the ideal solution for repairing corruption can help with immediate effects. 

The above image is of a B tree index structure. Here, B stands for balanced. It is a multi-level structured index having different levels as root, intermediate, and leaf.

To justify our statement “It is a structured index”, we can see data pages at the leaf level. This states that:

• The actual physical data is accommodated in the clustered index.
• The logical order of the table is defined by clustered Indexes.

If we look at the non-clustered index, it is also quite similar but it points towards the clustered index of its own data. Further, we’re going to execute SQL Server defragment index script, pay attention as the article is going to be slightly technical. Thus, you can easily execute the SQL Server Reorganize SQL Server Rebuild tasks.

Also Read: Learn How to Fix Missing Indexes in SQL Server – Get Missing Data Back

How to Rebuild and Reorganize Indexes in SQL Server? – Basics

Now, before we move on to the solution, we need to first understand what is actually Index fragmentation. This way we can easily get to the crux of the matter. 

Index fragmentation is quite a natural & common phenomenon that makes us explains the situation of non-contiguous data. Frequent editing in the database, deleting, adding, and updating can cause this.

In simple terms, a B-tree index can be fragmented when there’s a miss-match between the logical & physical ordering of the index pages. For instance, when we Add rows to a table, the index becomes fragmented. SQL database quickly modifies all the indexes involved with that one. Now your existing pages can split to make room for the new rows.

Adding a row to a page with insufficient space leads to new page creation. However, the new page can be the last in order but may contain data from previous pages. This creates confusion for the database & increases processing time. Hence, results in the degradation of performance. This is why users execute the reorganize index SQL Server operations.

Now, we can say that there are particularly two types of data fragmentation. For executing SQL Server defrag all indexes we need to know more about it. Below are the types:

• Logical Fragmentation
  Differences in the logical & physical order of pages. Evidently, it increases the number of physical reads.
• Internal Fragmentation
  Free spaces in the index’s data pages. Therefore, it results in an increase in the logical reads.

Users need to learn how to identify and resolve SQL Server index fragmentation to solve the issue. However, this isn’t a simple task as it contains the risk of SQL Database Corruption. We have several solutions to solve this issue like rebuilding & reorganizing the SQL databases.

Reorganize and Rebuild Index in SQL Server is the Solution

Reorganizing Tasks is the first option that users have. It simply arranges the index pages in a more simplified manner. To perform this below are the instructions:

• Try to tamp down the rows of index together & deallocate some of the pages.
• Change the remaining pages in tiny transactions until all of the pages get back into order. By the end, your logical order of indexes will be able to replicate the physical order of indexes.

IT experts consider this method in order to save space as it is efficient. It requires a temporary storage page of just 8KB. So, this is how to reorganize index in SQL Server safely.

In case of highly fragmented, this task can take quite a good time, in fact, longer than the re-index task. Although, your table won’t get locked during the operation. So that’s a relief for users if they want SQL Server index defrag quickly.

If we’re executing a really long reorganizing task, we can pause it for usual work & then resume it. This seems to be a good feature for users so far. Moreover, don’t forget to make a plan for RTO and RPO in SQL Server for Disaster Recovery & Management.

How to Rebuild and Reorganize Indexes in SQL Server – In-Depth

Now after the reorganizing task, it’s time for the Rebuilding Task for users. This task is totally different from the previous one. It simply creates a new index in the database & then erases the old one. Regardless of the fragmentation in the old index, this can happen. Users need to make sure that their database holds enough space for this new index. It can help us for SQL Server defrag all indexes.

Users can get to see the two options for the rebuilding operation. One of them is online &  the other one is offline. However, only the enterprise version of the SQL Server supports the online type. On the other hand, the standard version only supports the offline type.

If your index will be kept online during the operation, it is said to be the online mode. Otherwise, it’s the offline mode, when it is kept on the system. We can execute SQL Server defrag indexes on both.

Rebuild SQL Indexes Offline:

• Here, your pages are moved to make them contiguous physically.
• Your table will be locked & you can’t access it during the operation anyhow.
• Interrupting the process in between can result in the loss of data. It states that this task at a single index level is atomic.

Rebuild SQL Indexes Online:

• This procedure is exactly like the offline one. It just has very minor differences.
• The online rebuild locks the underlying table or index simply.

How to Identify and Resolve SQL Server Index Fragmentation?

We all must know the degree of fragmentation before actually starting any procedure. It can help us solve the issues with utmost perfection. To get the degree of defragmentation we can use DMF by sys.dm_db_index_physical_stats. In the below image you can see how to know the fragmentation degree percentage.

Selection of the right procedure for the defragmentation can be made as per Microsoft’s books online. SQL Server defragment index script might be required later. So users must be prepared.

• Defragmentation isn’t required if the degree of fragmentation is less than 5%.
• Reorganizing is a piece of advice in the case of 5% to 30% of fragmentation.
• If having more than 30% of fragmentation, rebuilding is the ideal choice.

Users need to understand that learning rebuilding is equally important as learning how to reorganize all the indexes in SQL Server. There should be wise decision-making among users.

Reorganize and Rebuild Index in SQL Server Using the T-SQL Command

Let’s start learning SQL Server Defrag Indexes using the manual query method. Here, we are going to use the SQL Server script to rebuild fragmented indexes safely. Now we are going to understand this method with the help of an example. Let’s say that we have an OrdTes table with the index IDX_OrdTes_DateDoc in our database. You can take the help of the below image to understand better.

Now, either we need to rebuild the indexes or reorganize. So we can simply organize the SQL index using the first T-SQL syntax & then by using the second one, we can easily use the second one.

If we need to learn how to reorganize all the indexes in SQL Server at once, this command will help us do that. Let’s move to the next section to know even more.

How to Reorganize All the Indexes in SQL Server – SSMS Method

For SQL Server index defrag using the SQL Server management studio, follow the below steps in detail. Do not mess up & skip any step to avoid data loss & corruption in SQL Reorganize tasks.

Reorganizing using SSMS

1. Go to Object Explorer >> Expland Database having table with a fragmented index.
2. Open table Folder >> Expand Table >> Righ-click index Folder >> Select Reorganize option.
3. Verify that the index has all the correct indexes >> Now Select the Index & Click on the Delete button.
4. Enable the “Compact large object column data” checkbox here.
5. Click on the Ok button.

Rebuilding using the SSMS

1. Go to Object Explorer >> Expland Database having table with a fragmented index.
2. Open table Folder >> Expand Table >> Righ-click index Folder >> Select the Rebuild option.
3. Verify that the index has all the correct indexes & Click on the Ok button.
4. Enable the “Compact large object column data” checkbox here.
5. Click on the Ok button.

Now we have successfully learned how to rebuild and reorganize indexes in SQL Server with utmost accuracy. Now that we have successfully used the SQL Server script to rebuild fragmented indexes, it’s time to know about our maintenance plan.

SQL Server Defrag All Indexes in A Maintaince Plan

As we know that the importance of a defragmentation strategy is quite high. Let’s make a maintenance plan for it. As we know how to reorganize index in SQL Server, now we just need to set up a plan. This way we can automatically perform the required operation without much hassle.

To make a new plan, Go to the Management Menu, Right Click on the Maintenance Item. Now, simply Click on the New Maintenance Plan option.

Now, just Drag & Drop “Reorganize Index Task” and “Rebuild Index Task” to the center of the screen. Also, follow the SQL Server defragment index script in the previous section carefully to reach these steps.

After adding both the tasks, we just need to set them accordingly, You can check that below.

Set SQL Reorganize Task in the Maintenance Plan:

• Allot the database on which the operation is going to be executed.
• Select the reorganize operation for only the necessary base, user database, or system database.
• Select the conditions under which you want to reorganize the indexes. For example: after a fixed percentage of fragmentation.

Set Rebuild Rebuild Index SQL Server Task in the Maintenance Plan:

• Allot the database on which the operation is going to be executed.
• Select the rebuild operation for only the necessary base, user database, or system database.
• Select the conditions under which you want to rebuild the indexes. For example: after a fixed percentage of fragmentation.

Finally, save the Maintenance Plan by clicking on the Ok button. In addition, schedule it to run on a particular frequency. This is how we can learn how to reorganize index in SQL Server as well as rebuild it.

Conclusion

Finally, we can say that we know how to reorganize and rebuild index in SQL Server. Now we just have to take care of one thing. When we execute SQL Server defrag indexes tasks, our files are at maximum risk. We need to be prepared for the worst-case scenario.

SQL Database Recovery Tool is one such tool that can recover & repair corrupted & lost data files in such situations.  With this tool, there is no need to rebuild index SQL Server using manual ways. This can be a perfect backup plan for users after learning how to reorganize all the indexes in SQL Server.

Also Read: Introduction to SQL Server Error 15105 & Best Ways to Fix It