SQL Server 2016 New Maintenance Plans

SQL Server 2016 Reindex and Reorganize New Features

SQL Server 2016 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.