SQL Server Maintenance Plans Rebuild Index Task


By:   |   Updated: 2019-12-13   |   Comments   |   Related: More > Indexing


Problem

It is well-known that correctly designed indexes are key for SQL Server database performance. However, in order to fully derive the benefits of the indexes, it is important to maintain them periodically. Therefore, properly organized and regularly scheduled index maintenance operations play a crucial role in improving database performance. This article is devoted to one of the index maintenance tasks, Rebuild Index using SQL Server Maintenance Plans.

Solution

Usually, in transactional databases, data is regularly modified, which also changes how index data is stored. As a result, indexes get fragmented. In turn, the fragmentation affects the efficiency of indexes and therefore could impact performance. Thus, to make the usage of indexes more optimal, they should be defragmented regularly. To do so, we can either reorganize or rebuild the indexes

As compared to rebuilding, reorganization is a more lightweight operation. It is an online operation and not only defragments the indexes, but also compacts them based on the existing fill factor settings.

Rebuilding indexes is a more resource-consuming process as it drops and recreates indexes. Additionally, a different value for fill factor can be set to optimize the index storage. This operation can be either online or offline depending on the index type and the version of SQL Server. The rebuild index operation automatically updates statistics as well, unlike the reorganize index operation.

Discussing the details of index architecture and the rebuild and reorganize operations is out of the scope of this article, but you can read more about rebuilding and reorganizing indexes.

Now we are going to illustrate how to design a rebuild index task via SQL Server Maintenance Plans.

Designing a SQL Server Maintenance Plan Rebuild Index Task

Let’s assume that we need to configure the Rebuild Index task for all indexes in all databases of our instance. We will use the Maintenance Plans Wizard to configure these tasks. The version of SQL Server used is SQL Server 2017.

In SQL Server Management Studio (SSMS), we choose Management > Maintenance Plans and then right-click and select Maintenance Plan Wizard.

maintenance plan wizard

After clicking on Maintenance Plan Wizard, the following window will appear.

maintenance plan wizard info screen

If we do not want to see this starting screen every time, we can click on the checkbox "Do not show this starting page again".

After clicking Next, the next window appears where we give a name and description to our task.

maintenance plan wizard properties

Additionally, we set the schedule for the task by clicking the Change button in the bottom right-hand corner.

Choosing the schedule of the Rebuild Index task depends on how quickly indexes get fragmented which is hard to say without creating a baseline for comparison.  It is also desirable to run this task when the database is not busy as it is a resource-consuming operation. We will set it to run weekly, every Sunday at 1:30am, but this could be run more or less frequently depending on fragmentation levels.

maintenance plan wizard schedule

 Here we can click OK and then Next to move to the screen where we choose the tasks.

maintenance plan wizard tasks

Above, we choose Rebuild Index and move forward by clicking Next.

maintenance plan wizard task order

As we have only one task here, we just click Next to continue.

In the screen below, we choose "All databases". It is also possible to choose specific databases and specific tables or views in the particular database. As mentioned earlier, we are using SQL Server 2017 to demonstrate our example and we can see that there are various options for the Index Rebuild operation.

maintenance plan wizard define task

 To compare, the same screen in SQL Server 2014 has very limited options. In the picture below, on the left side is SQL Server 2017's Rebuild Index screen and on the right side is SQL Server 2014.

maintenance plan wizard comparison

The difference is easily visible. For instance, in the Maintenance Plans of SQL Server 2017, it is possible to rebuild only those indexes where fragmentation level is higher than a specific percentage and has more pages than specified in "Page Count". Defining "Index Stats Options" as well has become available in SSMS. These options define how much resources should be used to gather index statistics. The faster the scan type, the lower the precision. We leave the default value of this option "Fast".

Even more, it is also possible to consider only those indexes that are used recently by setting the value of the "Used in last" option. This variety of options definitely makes the Rebuild Index task more flexible and optimal by rebuilding only the indexes which are really need to be rebuilt. In contrast, if we need to consider these options in older versions of SQL Server, we need to use T-SQL code as the Maintenance Plans wizard options are limited to only resetting the Fill Factor ("Change free space per page to"), choosing between sorting the result in the tempdb or not and keeping indexes online or offline during the rebuild process.

Microsoft recommends rebuilding indexes where the fragmentation level is more than 30%, so we will leave the above settings and click Next.

maintenance plan wizard report options

After choosing the report options, we click Next.

maintenance plan wizard review

Then, we complete the configuration by clicking Finish.

maintenance plan wizard finish

Modifying an Existing SQL Server Maintenance Plan

Under the Maintenance Plans in SSMS, we can see our plan. If we want to edit it, we can right-click on it and choose Modify.

maintenance plans

The Maintenance Plan Designer will open where we can modify the task.

maintenance plan modify

Modifying a SQL Server Maintenance Plan

To execute our task, we can right-click on the plan and choose Execute.

maintenance plan run

We can also run the task by running the corresponding job in SQL Server Agent.

maintenance plan run

After it completes, we can see the job was successful.

maintenance plan run

Hence, we have configured and tested the Rebuild Index task. In the next article, we will learn about the Reorganize Index and Update Statistics tasks.

Conclusion

To sum up, by using Maintenance Plans it is possible to design powerful index maintenance tasks. Moreover, unlike older versions of SQL Server Maintenance Plans, in the newer versions, the Rebuild Index task includes more options. This, definitely, makes it possible to create more robust index maintenance tasks without using T-SQL code. Having said that, there are still some limitations in Maintenance Plans, and for creating much more complex, flexible index maintenance solutions the use of manually written T-SQL code is essential.

Next Steps

For more information about the discussed topic, please follow the links below:



Last Updated: 2019-12-13


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Building SQL Server Indexes in Ascending vs Descending Order

SQL Server Auto Update and Auto Create Statistics Options

Script out all SQL Server Indexes in a Database using T-SQL

Difference between SQL Server Unique Indexes and Unique Constraints

Creating Indexes with SQL Server Management Studio








get free sql tips
agree to terms


Learn more about SQL Server tools