SQL Server Maintenance Plan Reorganize Index and Update Statistics Tasks

By: Sergey Gigoyan | Comments | Related: > Indexing

Problem

In a previous article, we configured the Rebuild Index task using the Maintenance Plan Wizard. As mentioned in that article, rebuilding indexes is a resource-consuming operation as it drops and recreates indexes. Thus, in terms of performance, sometimes an alternate option for fixing index fragmentation can be more effective. This option is index reorganization, which is a less resource-consuming process, but has limited options for index defragmentation. Unlike the index rebuild operation, the Index Reorganize task does not update statistics, for example, and it is impossible to set the fill factor for the index in this task. In this article, we will explore how to configure the Reorganize Index and Update Statistic tasks.

Solution

Usually, when indexes are not too fragmented (as suggested by Microsoft, when fragmentation level is between 5% and 30%), it is recommended to reorganize indexes instead of rebuilding them. Additionally, after reorganizing indexes it is reasonable to update the statistics as this operation does not update the statistics like the index rebuild operation. To learn more about index rebuild and reorganize operations, review this tip.

It is also recommended to read the previous article of this series - Rebuild Index Task before reading this one. Let’s move on to creating the Reorganize Index task via the Maintenance Plans and then Update Statistics task.

Designing the SQL Server Reorganize Index Task

The first steps of configuring the Reorganize Index task is quite similar to the Rebuild Index task. First, we will start with the "Maintenance Plan Wizard":

Obviously, we will give the corresponding name to the task and will choose the daily schedule as it is less resource-consuming operation as compared to rebuilding indexes and we prefer to run it more often:

Actually, we have chosen a weekly schedule, but have chosen all days despite Sunday as on Sundays we have a Rebuild Index task configured. In the next step, we choose the "Reorganize Index" task and move forward:

We have chosen "All Databases" to reorganize all indexes in all databases of our instance. In the configuration screen, we can see that like in the case of rebuilding indexes, we have several options:

Here we can also choose to reorganize indexes depending on their fragmentation level, used page counts and last used time.

Choosing the scan type is also available. We can see that "Compact large objects" is selected which means that space will be deallocated for tables and views if possible. According to Microsoft’s best practices, it is recommended to reorganize indexes if their fragmentation level is >15% and <=30% (if >30%, a rebuild should be done).

We leave default values here as well. The possibility to configure these options are missing in older versions of SQL Server. The picture below illustrates the differences between the same screen in SQL Server 2017 (on the left) and SQL Server 2014 (on the right):

We will move forward by clicking "Next" and reach the final step where we will click "Finish" to complete:

Now we can see that the ReorganizeIndexes plan has been added under "Maintenance Plans" and the corresponding job has been added under SQL Server Agent "Jobs":

Designing the SQL Server Update Statistics Task

In order to execute a query, SQL Server Query Optimizer tries to choose the best execution plan. To do so, it evaluates the relative costs of the potential execution plans using the column and index statistics. The statistics maintained by the SQL Server Engine, as their names suggest, contains statistical information about the distribution of values in columns of a table or indexed view. Based on this information, the Query Optimizer chooses which plan to use. For example, using the statistics, the Query Optimizer can decide whether to use an Index Scan or Index Seek operation in a particular query.

If the database’s AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS options are turned on, the SQL Server Engine creates and updates statistics automatically. However, after some database maintenance operations, it is important to update statistics manually. Particularly, after a Reorganize Index as the update statistics operation will improve the index use by providing the optimizer with better data when creating an execution plan.

Therefore, in our maintenance plan, we will add an Update Statistics task just after the Reorganize Index task. Let’s do it via the Maintenance Plan Designer. We can do it by right-clicking on the plan and choosing "Modify" or by double-clicking on it:

Then, drag and drop "Update Statistics Task" from the Toolbox to the design surface area:

To configure this task, we can double-click on it:

We choose "All databases, leave the default values of two other options and click "OK". Then we join the Reorganize Index Task with the Update Statistics Task by the precedence constraint (the green arrow). If we double click on that constraint, we can see that the Update Statistics Task will start after the previous, Reorganize Index Task, is successfully completed:

maintenance plan precedence constraint editor

Then we should click on the "Save" button to save our modifications.

This task can be executed like the previous - Rebuild Index task – by right-clicking on the task and choosing the "Execute" or by right-clicking on the corresponding job and the on "Start Job at Step…":

After this runs, all fragmented indexes within the ranges we setup above in all databases have been reorganized and statistics has been updated.

Conclusion

All in all, we have configured both the Rebuild Index and Reorganize Index tasks via the Maintenance Plans Wizard. As we can see, the Maintenance Plans have become more flexible in the newer versions of SQL Server allowing us to configure more options via the UI. Nevertheless, if it is needed to have more complicated logic in the maintenance tasks, T-SQL code can be more helpful. This is because the T-SQL code allows not only to configure everything which can be configured via the UI, but also to develop more complex tasks and logic.

Next Steps

To read more about the discussed topic, please follow the links below:

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips