By: Dinesh Asanka | Updated: 2019-05-14 | Comments (1) | Related: > Maintenance
Problem
Indexes are a key element to improve SQL Server database performance. However, simply creating an index isn't all that needs to be done. As a Database Administrator, you need to maintain indexes regularly to make sure you are getting the best performance. Index rebuilding and reorganizing are the two methods to maintain indexes and improve database performance. If you search for index rebuilding, you will find many complex scripts on the web, but this can also be done using SQL Server Maintenance Plans which we will discuss in this article.
Solution
Once indexes are built, you need to setup tasks to maintain the indexes, because they become fragmented which happens when data is inserted, updated and deleted in a table. There are two maintenance routines that can be performed, either an index rebuild or an index reorganization.
Database indexes can be fragmented in two ways, internal and external fragmentation. Internal fragmentation is when there is free space on a page, due to deletes, updates and inserts, and there is more free space on a page than required. External fragmentation is when database logical and physical pages are order differently. An index rebuild simply drops and recreates the index which means that index rebuild will solve both the internal and external fragmentation. An index reorganize only solves external fragmentation by moving pages around.
There are several ways to rebuild or reorganize indexes and this tip we are going to look at how this can be done using Maintenance Plans. In this tip we will look at the options are that are available in SQL Server 2016 and 2017. In older versions, not all of the options are available.
SQL Server Maintenance Plans
To access Maintenance Plans, in SSMS, open Object Explorer and then expand the Management folder and you will see the Maintenance Plans folder, this will be empty if no maintenance plans have been created. In the below image, it shows two different maintenance plans that I have already setup.
Creating a SQL Server Maintenance Plan
To create a new maintenance plan, right click on Maintenance Plan and select New Maintenance Plan and the following window will open.
We need to drag and drop tasks from the Toolbox (to open the toolbox use Ctrl + Alt + X) into the gray workspace area on the bottom as shown in the below screenshot.
The tasks we will be looking at are Rebuild Index Task and Reorganize Index Task in the Toolbox as shown below.
Configuring SQL Server Rebuild Index Task
After you drag the Rebuild Index Task into the workspace, double click on it to open the window below. The following is the Rebuild Index Task properties which can be configured depending on your needs. These options will be discussed below.
Connection
This selects which SQL Server you want to execute the index rebuild operations. Click on the New button to open the window below. On this screen, basic connection details should be provided as shown below.
Database(s)
Next, provide which databases you want to perform the task on.
Although there is an option to rebuild indexes for all databases, I find it is better to be selective so that you can plan the index rebuilds. You can create multiple maintenance plans to break this out which could help with scheduling and troubleshooting. If you have small databases, you can use the All user databases option. It is also a good idea to rebuild indexes for the system databases at least once a month, which can be done in a separate maintenance plan.
Object and Selection
If only one database is selected, then the Object and Selection option are available.
From the Object option, you can select what objects need to be rebuilt. There are three options, Table, View, and Table & View. If either Table or View is selected, you can select the tables or views in the Selection area.
If there are large indexes which need to be rebuilt or you need to rebuild specific indexes more frequently you can use these options to have better control over your index rebuilds.
Free Space Options
This is also called Fill Factor options. The Fill Factor determines the percentage of free space to keep on a page after the rebuild. By using a fill factor with some number like 80%, this could avoid frequent page splits which can cause performance issues and fragmentations.
It is important to note that what is shown in the index rebuild maintenance plan is inverse of Fill Factor. So, if the Fill Factor is 80% you need to specify 20% here. If you specify 80%, this means that you will end up with pages that have 80% free space.
The Default free space per page option uses the value you can find in the Server Properties value as shown below. You can fine this value by right clicking on the instance name and select Properties.
There is an option to override the default setting by selecting the second option. It is important to note that this setting is maintained only at the index rebuilding and does not maintain during the data insertion and updating. By selecting the Pad Index option, you are applying the fill factor to the intermediate levels of the indexes as well.
Sort Results in Tempdb
Rebuilding an index is not only a processor consuming task but also an I/O intensive task. By setting the Sort Result in Tempdb option, SQL Server will use the tempdb system database to store the intermediate results. When this option is not set, which is the default behavior, the database is used. If you are setting this option to ON, make sure that you have enough space on the tempdb drive. If you don't use this option, make sure you have enough space in the database drive. For large databases, it is better to have this option set to ON.
MAXDOP
The maximum degree of parallelism (MAXDOP) configuration option defines how many processors can be used to build the indexes. Since index rebuilding is a resource intensive task, by default it will use multiple processors. Since this will impact the performance of the other database operations, the MAXDOP value can be set to a customized value so that large index rebuild operations will not impact other database operations.
Keep Index Online
The Keep index Online option allows you to keep the existing index available while it is being rebuilt. If this is not set, when the clustered index is being rebuilt, users will not be allowed to access the table. When this option is not set on, you should perform the index rebuild during off peak hours so that there will not be as much impact to the database applications.
This option is available only in the Developer and Enterprise editions. If you set this option in any other editions of SQL Server the index rebuild operation will fail. So, you need to be very careful when setting this option as the error message does to tell you the exact error.
Some data types such as text, ntext, image and XML do not support online index rebuilds. In these cases, there is an option to ignore rebuilding these indexes or build them in offline mode which will block users from accessing the index while the index rebuild occurs.
Index rebuilds can use a Low Priority setting which means that if an index build thread causes a deadlock, the index rebuild will become the deadlock victim and the process will terminate. This ensures that more important operations do not become the deadlock victim.
If you don’t want index rebuilds to block other processes for a long period you can select the Abort After Wait option and specify how long to wait before the index rebuild is terminated. You can select whether the blocking is from the index rebuild or from another process.
Index Stats Options
The SQL Server database engine uses statistics to determine which execution plan to use when a query is compiled and executed. Having the latest statistics will improve database performance. When the index is rebuilt, index statistics are updated and this option determines how much of the data should be used to update the statistics.
Optimization Options
There are a few options to improve the performance of index rebuilding. For example, if you are rebuilding indexes for small tables and unused tables again and again, it is time-consuming and probably not very helpful. Therefore, these options are available as shown in the below image. This is saying that if the index is over 30% fragmented then the index should be rebuilt. Also, the page count says if there are 1000 pages or more then rebuild the index. A page is 8K bytes, so a setting of 1000 would be 8MB.
By using the above settings, it can be decided that only indexes with fragmentation with more than some value will be rebuilt. Also, only large indexes will be rebuilt if the Page Count options are specified. Also, there is an option to rebuild indexes which have been used in the last X days.
Scheduling
When the index rebuild is configured, the next step is to schedule it to run on the required schedule. Click on the calendar icon and this will open a new window so you can specify when the maintenance plan should run.
As shown in the above figure, multiple schedules can be created. When this schedule is created, a SQL Server Agent Job will be created with the given schedule.
Reorganize Index Task
Similar to rebuilding indexes, there is an option for Reorganizing indexes as shown below.
Most of the settings are the same as the Reindex Index Task settings.
Compact Large Objects
This option will deallocate space used for the index if the space is no longer required and therefore will free up space in your database.
Summary
Maintenance plans are a handy option for users who are not comfortable with T-SQL scripting and need an easy way to maintain indexes. All the required options for Index Rebuild and Index Reorganize are available in maintenance plans including online options, tempdb options, scheduling and more.
Next Steps
For further details please visit the following links.
- Fixing Index Fragmentation in SQL Server
- Script to Manage SQL Server Rebuilds and Reorganize for Index Fragmentation
- SQL Server script to rebuild all indexes for all tables and all databases
About the author
Dinesh Asanka is a 10 time Data Platform MVP and frequent speaker at local and international conferences with more than 12 years of database experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-05-14
