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…
Provide the appropriate name of the maintenance plan.
Drag and drop the index rebuild task from the maintenance plan toolbox. Rename it to the Nightly Index Maintenance job.
Right-click on the index rebuild task and click on Edit…
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.
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
- 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
- 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
- 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
- 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
- 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
- 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
- Do not rebuild the indexes
- Rebuild index offline
- 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
- 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
- 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.
The maintenance plan looks like the following image:
Now, let us schedule the maintenance plan. To do that, click on the calendar icon from the menu.
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.
Click OK to save the schedule. Save the maintenance plan. You can save the maintenance plan from the menu.
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.
Let us test the maintenance plan. Right-click on the Index Rebuild Job and click on the Execute option.
The maintenance plan execution starts.
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!
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022