Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Database Maintenance Plan Improvements


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


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:

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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools