Know How to Rebuild Indexes in SQL Server Database

Amanda Stanford | December 1st, 2018 | SQL Server

The main function of a database administrator (DBA) is to maintain the database. Indexes play an important role as they are one of the important aspects to high performance in SQL Server. Mainly SQL Server maintains indexes after any data modifications, still, maintenance of indexes are still required, due to Fragmentation. Index fragmentation affect the performance of SQL Server, so it is recommended to monitor the level of fragmentation.
Index fragmentation occurs in the form of gaps in data pages and logical fragmentation. Thus, the only way to remove the unnecessary space and logical fragmentation is to rebuild indexes in SQL Server.
In the following segment, we are going to discuss a step-by-step procedure to let users understand how to rebuild indexes in SQL Server in an absolute manner. Before that, let us know when and how often should you restore indexes in SQL server.

When to Rebuild Indexes in SQL Server?

As the indexes become fragmented so, the performance of all indexes is degraded. The Rebuild Index task is a very good option to rebuild indexes to remove the logical fragmentation and free space, and updating statistics. It is important to schedule tasks for timely rebuilding indexes in SQL Server database.

Otherwise, the Rebuild Indexes in SQL Server will become a resource-intensive task. Moreover, if the index gets rebuilt, locks can be placed on index, prevent someone from accessing it while rebuilding occurs. If anyone tries to access index in order to return actual results can be temporarily blocked before the rebuild is complete. For instance, the Rebuild Index task is examined the offline activity, to be run when some people want to access the database. Generally, this means during the schedule maintenance window.

Nightly, if needed: If index gets fragmented fast, and users have a maintenance window every night that allows to run Rebuild Index task, with all other maintenance tasks; then do so. The index fragmentation will degrade overall performance of each index. As such, users have a nightly maintenance window, rebuilding will not harm, and also help to boost the performance of server.
Weekly, at minimal: If users cannot perform this task every night, then, at minimum, it will run once a week, during the maintenance window. If users can wait more than a week, risk hurting the SQL performance due to the negative effect of empty space and logical fragmentation.
Consider other alternatives: If users do not have a maintenance window long enough to run the task at least once a week, then they can consider other alternatives like :

Note: If you want to repair your corrupted Indexes then you can take the help of SQL Database Recovery Software.

Download Purchase Now


How often to Rebuild Indexes in SQL Server?

The speed of index fragments depends on how it is used and will vary from one database to another. Let us consider the percent of index fragmentation:

  • avg_page_space_used_in_percent: This column can store total amount of space that is used on a particular page. For e.g, the specific index may have 50% space used, which means only half of space available on a data page, on an average, it is used to preserve rows of data
  • avg_fragmentation_in_percent: This column can store the degree of a logical fragmentation of index, as a percentage. For example, a particular index might be 80% fragmented, which means, on average, 80% of data pages physical ordering does not match the logical ordering

If users will be able to track this data for a period of time, they will determine how quickly all indexes fragments, and how often you should rebuild them. But, if at this level, there might be chances that users can use scripting techniques to rebuild all indexes rather than Maintenance Plan Wizard.

How to Rebuild Indexes in SQL Server Manually

Go through these two manual approaches that will help to rebuild indexes SQL server database. Have a look:

  • Using MS SQL Server Management Studio:

a) Under the Object Explorer pane, expand the SQL Server, and then Databases node
b) Then, click the specific database with fragmented index
c) Expand each Table node, and table with fragmented index
d) Expand the particular table>> Indexes node
e) Now, right-click on fragmented index and select Rebuild option

f) Click OK button and wait for a process to finish

  • Using Transact-SQL to Rebuild Indexes in Table

Provide the suitable database and table information. After that, you can execute the following code in SSMS (SQL Server Management Studio) to rebuild indexes in SQL Server on particular table:


Summing Up

Index fragmentation is a critical issue as it can cause performance problems in SQL Server database if they are not rebuild timely. One simple way to remove index fragmentation is to Rebuild Index task regularly, which helps to rebuild all indexes in SQL database and make performance consistent. Thus, in this blog, we have discussed two different ways that will help to understand you how to rebuild indexes in SQL server. Make sure you have follow each step very carefully.