SQL Server 2016 Database Maintenance Plan Improvements

By:   |   Comments (1)   |   Related: > SQL Server 2016


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?


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:

Database Maintenance plan in SQL Server 2016

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.
Reindex Maintenance task

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta 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

Comments For This Article

Tuesday, June 19, 2018 - 6:59:30 AM - Jack Whittaker Back To Top (76246)

 I am delighted to see the new functions, and thank you for the explanation.  But something worries me

If I click on View T-SQL to see what the new code looks like, it comes up blank

And when the job runs, the Rebuild Index task runs in 00.00 seconds

Is it actually doing anything?

get free sql tips
agree to terms