SQL Server 2016: New possibilities for Index Maintenance Task

The new CTP 2.4 has been released some weeks ago by Microsoft. My colleagues and I have already blogged about some new functionalities. I will focus in this blog about the new possibility of Index Maintenance Tasks regarding Indexes.

Until SQL Server 2016, DBAs didn’t use often the Maintenance Plan provided by SQL Server for indexes. In fact, those two tasks, Reorganize Index and Rebuild Index were not enough detailed and DBA’s preferred to create their own jobs where they are able to define after which percentage of fragmentation an index have to be reorganized and after which one it have to be rebuild, even parallelize or not this process…

With the new CTP 2.4, we find directly this kind of options in the SQL Server Maintenance Plan.
Let’s have a look to those both Maintenance tasks.

Reorganize Index Task

We have now the possibility to:

• Scan type
  • define the level of scan used to find the fragmentation: Fast, Sample or Detailed
• Condition to reorganize
  • percentage of fragmentation after which the index will be reorganize
  • number of page minimum to execute the reorganization
  • index used in the last X days, otherwise the index will not be reorganize

Rebuild Index Task

• MaxDOP
  • possibility to specify the number of processor used during the rebuild
• Pad Index
  • possibility to enable or disable Pad Index if we change free space per page
• Low priority used
  • possibility when index is rebuild online to abort, after a certain number of minutes of wait, blockers or itself (dangerous…)
• Scan type
  • define the level of scan used to find the fragmentation: Fast, Sample or Detailed
• Condition to rebuild
  • percentage of fragmentation after which the index will be rebuild
  • number of page minimum to execute the reorganization
  • index used in the last X days, otherwise the index will not be rebuild

These enhancements give the possibility to choose what to do between a Reorganization or a Rebuild based on fragmentation’s percentage. Does it mean that scripts will be obsolete?… For the moment, I don’t think so 😉