Nisarg Upadhyay
drag-drop index maintenance task

Automate rebuilding SQL Database indexes using Maintenance Plans

April 9, 2021 by

Index maintenance is a vital part of a database administrator’s job. I have worked for few clients who often face performance issues in SQL Database and don’t have a dedicated DBA. I remember that one of our clients was facing performance issues. I logged in to their server and found that they did not set up any index maintenance jobs; therefore, many indexes were fragmented, causing the problem.

Many solutions can be used for index maintenance. We can use ola-hallengren index maintenance scripts for SQL databases or create custom scripts that can rebuild or reorganize based on the index fragmentation. We can create an SSIS package or SQL Server maintenance plan to automate the index maintenance.

This article explains how to automate the index maintenance using the SQL Server maintenance plan.

Create a Maintenance Plan to automate the index maintenance

We are using SQL Server Management Studio (SSMS) to create a maintenance plan. The option is under the Management node of SQL Server management studio. Right-click on Maintenance Plans and select New Maintenance Plan…

New maintenance plan to index maintenance of SQL database

Provide the appropriate name of the maintenance plan.

Specify maintenance name

Drag and drop the index rebuild task from the maintenance plan toolbox. Rename it to the Nightly Index Maintenance job.

drag-drop index maintenance task

Right-click on the index rebuild task and click on Edit…

Edit index maintenance task of SQL database

We want to create an index maintenance job that rebuilds indexes in all user SQL databases of the server. We are creating a job on a local SQL Server; therefore, choose a local connection. Click on Databases and select All User databases.

Select all User SQL database

In the Rebuild Index task dialog box, we can configure the various parameters to tune the index rebuild task. The details of the parameters are following:

Free space options

  1. Free space per page: This parameter is called Fill Factor. After index rebuild operation, the value of the Fill factor determines the free space in a page. When we select the Default free space per page option, the SQL Server sets the default value of the index fills factor parameter. You can view the value of the default fill factor in database properties
  2. Change free space per page: If you want to change the default fill factor value, you can specify it in the Change free space per page text box. If you want to keep the 80% free space per page, you must specify 20% in the text box

Advanced options

  1. Sort result in TempDB: When you set Sort to result in TempDB option ON, the TempDB will be utilized to store the intermediate result of an index rebuild operation. Here make sure that the TempDB is properly sized, and sufficient space is available
  2. MAX DOP: You can specify the number of CPUs that can be used to perform the index rebuild operation. During index rebuild, the CPU utilization increases, impacting the database’s overall performance, so it is advisable to set a customized value of MAXDOP
  3. Keep Index Online: When you set the Keep Index Online option ON, the index rebuild does not lock the table. When we rebuild the clustered index, the table will be locked, and applications or users cannot access it. In SQL Server enterprise edition, we can rebuild the index without impacting the performance
  4. Indexes that are not supported online rebuild index: You cannot do online index rebuild on columns with text, image, ntext, and XML data types. Here you can choose any of the following options
    1. Do not rebuild the indexes
    2. Rebuild index offline
  5. Abort after wait: When we are rebuilding the index, and it causes the deadlock in the SQL database, then we can abort the index rebuild operation. You can specify the maximum duration in the Max Duration textbox. SQL Server waits for the duration specified in the textbox, and if the operation does not complete, it aborts the index rebuild operation

Index Stats options

  1. Scan type: When we rebuild the index, the statistics will be updated. This option is used to determine the amount of data to be used to update the statistics
  2. Optime index condition: When the indexes are being rebuilt, the resource utilization increases significantly which reduces the performance of the SQL database. To avoid such issues, it is advisable to run index maintenance during off business hours and avoid unnecessary index rebuild. In fragmentation> text box, you can specify the percentage of index fragmentation. For example, if you specify 30, then the maintenance plan updates the indexes whose fragmentation is higher than 30%

I did not make any changes in the default settings of the rebuild index task. Click OK to save the configuration and close the dialog box.

Configure the index maintenance parameters

The maintenance plan looks like the following image:

Maintenance job pane

Now, let us schedule the maintenance plan. To do that, click on the calendar icon from the menu.

Menu to add sub plans in maintenance plan

The new Job Schedule dialog box opens. We want this index maintenance job to be executed every night at 1 AM so, I have changed the values accordingly.

Schedule the index maintenance job

Click OK to save the schedule. Save the maintenance plan. You can save the maintenance plan from the menu.

Save button in menu bar

The maintenance plan has been created successfully. You can view the maintenance plan in the management node of SQL Server management studio. You can view the agent job, which automates the index maintenance in the SQL Server Agent node.

View maintenance plan

Let us test the maintenance plan. Right-click on the Index Rebuild Job and click on the Execute option.

Execute Maintenance plan

The maintenance plan execution starts.

Executing the index rebuild job

The maintenance plan executes successfully.

Summary

This article explains how we can automate index maintenance of SQL database using SQL Server maintenance plans. In this article, we deep dive into the Index rebuild task of the maintenance plan. I have explained the configuration parameters and their purpose. In my next article, I will explain how we can automate the database consistency check task. Stay tuned!

Nisarg Upadhyay
DBAtools, Jobs, Maintenance

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

840 Views