MS SQL Database Maintenance: Index Rebuilding and Reorganizing

NOTE: The method of rebuilding indexes by using MS SQL Maintenance plans described in this document only works with the Standard and Enterprise versions of MS SQL Server (the Express version does not support this feature).

Indexes are a key element in improving the performance of the MS SQL Server database. Database administrators need to maintain indexes regularly to make sure best performance is achieved.

Index rebuilding and reorganizing are two methods used to maintain indexes and improve database performance. These procedures can be performed by using complex SQL queries or SQL Server Maintenance Plans in SQL Server Management Studio.

Once indexes are built, tasks need to be set up to maintain them, since the indexes become fragmented over time when data is inserted into, updated in, and deleted from a table.  

Reorganizing an Index

Reorganizing an index is less resource intensive than rebuilding an index. For this reason, index reorganizing should be your preferred index maintenance method, unless there is a specific reason to use index rebuilding. Reorganizing is always an online operation. This means that long-term object-level locks are not held, so queries or updates to the underlying table can continue during a reorganizing operation.

Rebuilding an Index

Rebuilding an index drops and recreates the index. Depending on the type of index and the MS SQL Database Engine version used, a rebuild operation can be performed either offline or online. An offline index rebuild usually takes less time than an online rebuild, but it holds object-level locks for the duration of the rebuild operation, thereby blocking queries from accessing the table or view while in process.

An online index rebuild does not require object-level locks until the end of the operation, when a lock must be held for a short duration to complete the rebuild. Depending on the version of MS SQL Database Engine used, an online index rebuild can be started as a resumable operation. A resumable index rebuild can be paused, thereby keeping the progress made up to that point. A resumable rebuild operation can be resumed after having been paused or interrupted, or aborted if completing the rebuild becomes unnecessary. 

NOTE: While an index is being rebuilt online, every modification of the data in the indexed columns must update an additional copy of the index. This may result in minor performance degradation of data modification statements during online rebuilding. If an online resumable index operation is paused, this performance impact persists until the resumable operation either completes or is aborted. Therefore, if you do not intend to complete a resumable index operation, abort it instead of pausing it.

Table of Contents

1. Checking Whether to Reorganize or Rebuild Indexes

Before performing the required operations for the indexes, knowledge of their level of fragmentation is required. This information can be obtained by using the default Index Physical Statistics report in SQL Server Management Studio.

Run this report, as follows:

1. Open SQL Server Management Studio, and select the required database containing activities (where its name is usually in the following format: <hostname>.EkranActivityDB).

2. Right click on the database, and select Reports > Standard Reports in the context menu that opens.

3. Run the Index Physical Statistics report.

4. The report then displays all the available indexes in this database along with their current levels of fragmentation, as well as the recommended operation, and much more.

A general rule is to reorganize indexes that have a fragmentation level of less than 30%, and rebuild indexes where fragmentation is 30% or higher. 

2. Creating and Scheduling a Maintenance Plan to Rebuild Indexes

Now that you know what the fragmentation level of your database is, you can create a maintenance plan as follows:

1. To access the Maintenance Plans in SQL Server Management Studio, open Object Explorer and expand the Management folder, and then right-click on the Maintenance Plans folder, and select New Maintenance Plan in the drop-down list that opens.

2. Enter a name for the new maintenance plan (e.g. "Index Rebuild Job" in the screenshot below).

3. Drag and drop the Rebuild Index Task from the Maintenance Plan Tasks toolbox, and rename it (e.g. to Nightly Index Maintenance, as shown in the screenshot below).

To rename it, right-click on the Rebuild Index Task, and then select Edit in the context menu that opens.

4. Next, an index maintenance job needs to be created that rebuilds indexes in your database containing activities (called "EkranActivityDB" ).

If you are creating the job on a local SQL Server, in the Connections field, “Local server connection “needs to be selected in the drop-down menu that opens, and in the Database(s) field, select "EkranActvityDB".

Do not make any changes to the default settings of the Rebuild Index Task.

NOTE: For large deployments, it is recommended to disable the "Keep index online" checkbox option. The database will then be disconnected from the server and will not be modified (i.e. will not receive any new data). When the rebuild process is complete, all the Syteca Clients will resume sending their data to the database from their local offline pools.

5. Click OK to save the configuration and close the dialog box.

The new maintenance plan is then displayed (as shown in the screenshot below).

6. Next, schedule the maintenance plan, by clicking on the calendar icon in the menu (as shown in the screenshot below).

The New Job Schedule dialog box then opens, where the maintenance plan is scheduled (e.g. the index maintenance job shown in the screenshot below is to be executed every night at 1 AM).

NOTE: In fact, there is no need to run the task every day, and we recommend scheduling it for once every six months.

7. Click OK to save the schedule, and then save the maintenance plan (which can be saved on the menu as shown in the screenshot below).

The maintenance plan is then created, and can be viewed in the Management folder of SQL Server Management Studio, where the agent job, which automates the index maintenance in the SQL Server Agent folder, can also be viewed.

8. Next, test the maintenance plan by right-clicking on the Index Rebuild Job, and selecting the Execute option in the context menu that opens.

Execution of the maintenance plan then starts (as shown in the screenshot below).