SQL Server Maintenance Plans is a SQL Server Management Studio built-in feature that helps in creating a workflow of variant database administration tasks, which can be run automatically using a predefined schedule or manually triggered by the user.
SQL Server Maintenance Plans allow you to use typical database maintenance tasks or customize your own task using a T-SQL script that runs on the local server or group of SQL Servers, providing more flexibility to the database administration tasks.
SQL Server Maintenance Plans can be found under the Management node of the SQL Server instance using the SQL Server Management Studio, from where these Maintenance Plans can be created or edited using an easy wizard-based process, as shown below:
The workflow of tasks created using the SQL Server Maintenance Plans can perform a variety of database administration tasks, such as rebuilding and reorganizing database indexes using a specific fill factor to maintain the equal distribution of database pages. You can also perform a database consistency check to detect and fix damaged databases pages.
The three main database backup types, Full backup, Differential backup and Transaction Log backup can be also configured and scheduled using the Maintenance Plans, allowing us to restore the database to a specific time in the case of data loss of corruption. Removing the database empty pages from the database data and log files can be easily configured and scheduled using the Maintenance Plans. The Maintenance Plans can also help the SQL Server Query Optimizer to create the best and most optimized query plans by updating the tables and indexes statistics, which is metadata information about the database data and distribution, to be up to date.
All these tasks that can be performed using the SQL Server Maintenance Plans are shown below:
SQL Server 2016, that comes with many new features and enhancements to the existing features, bring new enhancements to a number of SQL Server Maintenance Plans tasks including the indexes Rebuilding and Reorganizing tasks, in addition to the Check Database Integrity tasks. In this article, we will go through these new enhancements in details.
Index Maintenance Tasks
One of the most important administration task that every database administrator should care about is rebuilding database indexes. Frequent INSERT, UPDATE and DELETE operations will lead to highly fragmented data pages. These pages with gaps between it will increase the physical I/O processes due to visiting the disk subsystems more frequent, in order to make the fragmented requested data available in the memory storage. As a result, the query performance will be degraded.
Data pages fragmentation can be resolved by rebuilding and reorganizing the indexes regularly. You can easily go through the Rebuild Index and Reorganize Index Maintenance Plans, specify the databases that you will perform the index maintenance on and schedule that maintenance to be performed minimum on weekly basis. Specifying the index maintenance schedule is not an easy decision, as it depends on your database structure, how fast the data is fragmented, the SQL Server Edition, that allows you to rebuild the index online, and workload that specifies the available maintenance windows. Defragmenting the database indexes using the rebuilding and reorganizing Maintenance Plans will clearly enhance the overall database performance.
In the previous SQL Server versions, using the Rebuild Index and Reorganize Index Maintenance Plans tasks to fix the database indexes fragmentation issue is not highly recommended. This is because the Rebuild Index task will destroy all the indexes and build it again from scratch, without considering the fragmentation percentage. The same thing will happen in the Reorganize Index maintenance task, in which all indexes will be reorganized regardless of the fragmentation percentage. In this way, a large maintenance window will be required for the huge databases, in addition to the big resources that are required to perform these two resource intensive operations.
What makes customized T-SQL scripts more attractive instead of using the Maintenance Plans tasks to rebuild and reorganize the database indexes is the ability to control these heavy operations to be customized for our database workload and structure. We need to decide when to rebuild or reorganize an index depending on the fragmentation percentage of that index, as reorganizing the index that is lightly fragmented will resolve its issue without the need to destroy it and create it again by rebuilding it. This will help us in making the index maintenance process faster and consumes fewer resources, resulting in shorter maintenance window required for that maintenance.
SQL Server 2016 comes with new improvements to the index maintenance tasks functionality that provides us with more control to the Rebuild Index and Reorganize Index tasks. If you try to configure the Rebuild Index maintenance task in SQL Server 2016 instance, you will see additional options that we can configure to control the process of rebuilding the index, compared with the previous SQL Server versions.
The below window shows the Rebuild Index maintenance task configuration window with the additional enhancements outlined by the red boxes:
It is clear from the previous Rebuild Index configuration window that the new enhancements are performed on both the Advanced Options and the Index Stats Options sections. In the Advanced Options section, the included options are:
- The Pad Index checkbox, that is used to make sure that the fillfactor value specified for the indexes will also be applied to the intermediate level pages of the index, not only the leaf level pages.
- The MAXDOP checkbox is used to specify the number of CPUs that are used to rebuild the index, overriding the default Maximum Degree Of Parallelism value at the server level. This option will work only in the Enterprise and Developer SQL Server Editions.
-
The Low Priority Used checkbox is used to specify if the online index rebuild process will wait in the Low Priority queue, the new feature introduced in SQL Server 2014 version to control how the blocking mechanism, that is required by the online index rebuild operation in the Enterprise SQL Server Edition, will be handled. This new feature provides you with two control parameters; the MAX DURATION, in which you can specify the time, in minutes, that the online index rebuild process will wait in the low priority queue. If the online index rebuild process exceeds that time, the Abort After Wait parameter can be used to specify the action that will be performed after exceeding that time. The Abort After Wait parameter has three options,
- NONE in which the online index process will continue working after exceeding the specified time,
- SELF in which the online index process will be killed after exceeding the specified time,
- or BLOCKERS in which the online index rebuild process will kill all transactions that are blocking it from being processed.
The Index Stats Options section provides us with new parameters that helps in specifying which indexes will be rebuilt and the indexes that will be excluded from the rebuild process, based on the index statistics criteria using the sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats system DMVs internally. From this section you can control:
- The Scan Type, that controls how much precession is required for the index statistics, taking into consideration the amount of resources that will be consumed in gathering this specified amount of index statistics. The Scan Type can take one of the three options; Fast, Sampled or Details statistics gathering.
- The Optimize Index Only If, that is used to tune the filters that control which indexes will be refreshed and the indexes that will be excluded. You can specify the Fragmentation percentage threshold, the Page Count threshold and the Used in Last (days) threshold for the indexes that will be rebuilt.
The below window shows the Reorganize Index maintenance task configuration window with the additional enhancements outlined by the red box:
The Reorganize Index task is always single thread and online in all SQL Server editions so that we need to specify only the candidate indexes that should be refreshed. The Index Stats Options section in the Define Reorganize Index Task window provides us also with new parameters that help in specifying which indexes will be reorganized and the indexes that will be excluded from the reorganize process, based on the index statistics. Just in the same as the previously described parameters in the Rebuild Index task work, from this section you can control:
- The Scan Type, that can take one of the three options; Fast, Sampled or Details statistics gathering.
- The Optimize Index Only If, that can specify the Fragmentation percentage threshold, the Page Count threshold and the Used in Last (days) threshold for the indexes that will be rebuilt.
Database Check Integrity Task
The Check Database Integrity task performs a check on the logical and physical allocation and structural integrity of all database objects, with the ability to check the integrity of a single database or multiple databases, and whether to check the database indexes too, by running the DBCC CHECKDB T-SQL command. In this way, it will make sure that there is no integrity issues or corruption at the database pages’ level.
The following window shows the Database Check Integrity maintenance task configuration window with the additional enhancements outlined by the red box:
Starting from SQL Server 2016, the Database Check Integrity task configuration window includes the below three additional control parameters:
- The Physical Only checkbox, that is used to limit the integrity check to be performed only at the pages’ physical structure, record headers and the database allocation consistency levels only. This limitation helps us in reducing the time and resources required to perform the DBCC CHECKDB command on the huge databases.
- The Tablock checkbox, that allows the DBCC CHECKDB command to obtain short-term exclusive locks on the database, instead of using an internal database snapshot, speeding up the DBCC CHECKDB command execution, but decreasing the database available concurrency.
- The Max Degree Of Parallelism checkbox, that species the number of processors that will be used to run the DBCC CHECKDB command.
Conclusion
SQL Server Maintenance Plans are an old but good feature that helps us in automating large number of the most important database administration tasks. In SQL Server 2016, the number of enhancements are applied to the maintenance tasks that are responsible for rebuilding the database indexes, reorganizing the database indexes and checking the database integrity by providing more control to these tasks. In this article, we described these enhancements in details, where you can find how could you take benefits from these new options.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021