SQL Server 2016 Database Maintenance Plan Improvements

By: Rajendra Gupta   |   Updated: 2015-11-18   |   Comments (1)   |   Related: > SQL Server 2016

Problem

What new functionality is available in SQL Server 2016 for Database Maintenance Plans for reindexing, reorganizing indexes and DBCC CHECKDB? Are there any better features to fine tune processes to better manage SQL Server with limited downtime?

Solution

SQL Server 2016 Reindex and Reorganize New Features

SQL Server 2016 CTP 2.4 has improved the functionality for reindexing and reorganizing indexes using the Database Maintenance Tasks. Suppose we want to implement a reindexing plan such as:

Reorganize index: if fragmentation is > 15% and number of pages > 1000

Rebuild index: if fragmentation is > 30% and number of pages > 1000

If we open the Database Maintenance Plan from Management Studio in SQL 2016 CTP 2.4, we have these options to fine tune the indexes in scope:

We can fine tune the indexes in scope with these options:

• Fragmentation Level is > defined percentage of fragmentation
• Page Count > total no of pages in index
• Used in Last n number of days

SQL Server 2016 Rebuild Index Task Improvements

In SQL Server 2016, the Rebuild Index Task in the Database Maintenance Plan includes additional features outlined in red including:

• MAXDOP: Overrides the max degree of parallelism configuration option for the duration of the index operation.
• Pad_Index: Uses the PAD_INDEX option which ensures the fillfactor value is also used for intermediate level pages for the index.
• MAX_DURATION: Specify the number of minutes the online index rebuild will wait. If the MAX_DURATION value is reached, we can set what happens next based on the setting of ABORT_AFTER_WAIT, which can be a value of NONE, SELF or BLOCKERS:
  • NONE: Index operation will continue attempting the operation.
  • SELF: If the MAX_DURATION is reached, the operation (the online index rebuild) will be cancelled.
  • BLOCKERS: It will kill any transactions that are blocking the online index rebuild. BLOCKERS also requires ALTER ANY CONNECTION permission for the request issuing the online index rebuild.

SQL Server 2016 Database Check Integrity Task in Database Maintenance

If we look at the SQL Server 2016 Database Check Integrity Task in Database Maintenance shown above we can select these options:

• Include Indexes: Include indexes in the DBCC CHECKDB process.
• Physical only: Limits the check to the integrity of the physical structure of the page, record headers, and the allocation consistency of the database. Using this option may reduce run-time for DBCC CHECKDB on large databases, and is recommended for frequent use on production systems.
• Tablock: DBCC CHECKDB obtain table locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database. Using this option may help DBCC CHECKDB run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running.

Next Steps

• Read More about Maintenance Plan Wizard
• Try it out yourself! Download and install the SQL Server 2016 preview
• For more SQL Server 2016, read these other SQL Server 2016 Tips.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

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

View all my tips