SQL Server 2016维护计划增强功能

SQL Server Maintenance Plans is a SQL Server Management Studio built-in feature that helps in creating a workflow of variant database administration tasks, which can be run automatically using a predefined schedule or manually triggered by the user.

SQL Server维护计划是SQL Server Management Studio的内置功能，可帮助创建各种数据库管理任务的工作流，该工作流可以使用预定义的时间表自动运行，也可以由用户手动触发。

SQL Server Maintenance Plans allow you to use typical database maintenance tasks or customize your own task using a T-SQL script that runs on the local server or group of SQL Servers, providing more flexibility to the database administration tasks.

SQL Server维护计划允许您使用典型的数据库维护任务或使用在本地服务器或SQL Server组上运行的T-SQL脚本自定义任务，从而为数据库管理任务提供了更大的灵活性。

SQL Server Maintenance Plans can be found under the Management node of the SQL Server instance using the SQL Server Management Studio, from where these Maintenance Plans can be created or edited using an easy wizard-based process, as shown below:

可以使用SQL Server Management Studio在SQL Server实例的“ 管理”节点下找到SQL Server维护计划，可以在其中使用基于向导的简单过程来创建或编辑这些维护计划，如下所示：

The workflow of tasks created using the SQL Server Maintenance Plans can perform a variety of database administration tasks, such as rebuilding and reorganizing database indexes using a specific fill factor to maintain the equal distribution of database pages. You can also perform a database consistency check to detect and fix damaged databases pages.

使用SQL Server维护计划创建的任务的工作流可以执行各种数据库管理任务，例如使用特定的填充因子来重建和重新组织数据库索引，以维持数据库页面的均匀分布。 您还可以执行数据库一致性检查，以检测和修复损坏的数据库页面。

The three main database backup types, Full backup, Differential backup and Transaction Log backup can be also configured and scheduled using the Maintenance Plans, allowing us to restore the database to a specific time in the case of data loss of corruption. Removing the database empty pages from the database data and log files can be easily configured and scheduled using the Maintenance Plans. The Maintenance Plans can also help the SQL Server Query Optimizer to create the best and most optimized query plans by updating the tables and indexes statistics, which is metadata information about the database data and distribution, to be up to date.

还可以使用维护计划来配置和计划三种主要的数据库备份类型，即完全备份，差异备份和事务日志备份，以便在数据丢失损坏的情况下将数据库还原到特定时间。 使用维护计划可以轻松配置和计划从数据库数据和日志文件中删除数据库空白页。 维护计划还可以通过更新表和索引统计信息（即有关数据库数据和分布的元数据信息）来帮助SQL Server查询优化器创建最佳和最优化的查询计划。

All these tasks that can be performed using the SQL Server Maintenance Plans are shown below:

使用SQL Server维护计划可以执行的所有这些任务如下所示：

SQL Server 2016, that comes with many new features and enhancements to the existing features, bring new enhancements to a number of SQL Server Maintenance Plans tasks including the indexes Rebuilding and Reorganizing tasks, in addition to the Check Database Integrity tasks. In this article, we will go through these new enhancements in details.

SQL Server 2016具有许多新功能和对现有功能的增强，除了检查数据库完整性任务之外，还为许多SQL Server维护计划任务（包括索引重建和重组任务）带来了新增强。 在本文中，我们将详细介绍这些新的增强功能。

索引维护任务 (Index Maintenance Tasks)

One of the most important administration task that every database administrator should care about is rebuilding database indexes. Frequent INSERT, UPDATE and DELETE operations will lead to highly fragmented data pages. These pages with gaps between it will increase the physical I/O processes due to visiting the disk subsystems more frequent, in order to make the fragmented requested data available in the memory storage. As a result, the query performance will be degraded.

每个数据库管理员都应关注的最重要的管理任务之一是重建数据库索引。 频繁的INSERT，UPDATE和DELETE操作将导致高度分散的数据页。 这些页面之间有间隙，这将由于更频繁地访问磁盘子系统而增加了物理I / O进程，从而使分段的请求数据可在内存存储中使用。 结果，查询性能将降低。

Data pages fragmentation can be resolved by rebuilding and reorganizing the indexes regularly. You can easily go through the Rebuild Index and Reorganize Index Maintenance Plans, specify the databases that you will perform the index maintenance on and schedule that maintenance to be performed minimum on weekly basis. Specifying the index maintenance schedule is not an easy decision, as it depends on your database structure, how fast the data is fragmented, the SQL Server Edition, that allows you to rebuild the index online, and workload that specifies the available maintenance windows. Defragmenting the database indexes using the rebuilding and reorganizing Maintenance Plans will clearly enhance the overall database performance.

数据页碎片可以通过定期重建和重新组织索引来解决。 您可以轻松地完成“重建索引”和“重新组织索引维护计划”，指定要执行索引维护的数据库，并安排每周至少执行一次维护。 指定索引维护计划不是一个容易的决定，因为它取决于您的数据库结构，数据碎片的速度，SQL Server Edition（允许您在线重建索引）以及指定可用维护窗口的工作量。 使用重建和重新组织的维护计划对数据库索引进行碎片整理将明显提高整体数据库性能。

In the previous SQL Server versions, using the Rebuild Index and Reorganize Index Maintenance Plans tasks to fix the database indexes fragmentation issue is not highly recommended. This is because the Rebuild Index task will destroy all the indexes and build it again from scratch, without considering the fragmentation percentage. The same thing will happen in the Reorganize Index maintenance task, in which all indexes will be reorganized regardless of the fragmentation percentage. In this way, a large maintenance window will be required for the huge databases, in addition to the big resources that are required to perform these two resource intensive operations.

在以前SQL Server版本中，强烈建议不要使用“重建索引”和“重新组织索引维护计划”任务来修复数据库索引碎片问题。 这是因为“重建索引”任务将销毁所有索引并从头开始重新构建，而无需考虑碎片百分比。 在“重新组织索引”维护任务中将发生相同的事情，在该任务中，将重新组织所有索引，而不管碎片的百分比如何。 这样，除了执行这两个资源密集型操作所需的大资源之外，大型数据库还需要大型维护窗口。

What makes customized T-SQL scripts more attractive instead of using the Maintenance Plans tasks to rebuild and reorganize the database indexes is the ability to control these heavy operations to be customized for our database workload and structure. We need to decide when to rebuild or reorganize an index depending on the fragmentation percentage of that index, as reorganizing the index that is lightly fragmented will resolve its issue without the need to destroy it and create it again by rebuilding it. This will help us in making the index maintenance process faster and consumes fewer resources, resulting in shorter maintenance window required for that maintenance.

使自定义的T-SQL脚本更具吸引力而不是使用维护计划任务来重建和重新组织数据库索引的原因在于，能够控制这些繁重的操作以针对我们的数据库工作负载和结构进行自定义。 我们需要根据索引的碎片百分比来决定何时重建或重新组织索引，因为轻度碎片化的索引将解决其问题，而无需销毁索引并通过重建索引来重新创建。 这将有助于我们加快索引维护过程并减少资源消耗，从而缩短维护所需的维护时间。

SQL Server 2016 comes with new improvements to the index maintenance tasks functionality that provides us with more control to the Rebuild Index and Reorganize Index tasks. If you try to configure the Rebuild Index maintenance task in SQL Server 2016 instance, you will see additional options that we can configure to control the process of rebuilding the index, compared with the previous SQL Server versions.

SQL Server 2016对索引维护任务功能进行了新的改进，使我们可以更好地控制“重建索引”和“重组索引”任务。 如果您尝试在SQL Server 2016实例中配置“重建索引”维护任务，则与以前SQL Server版本相比，您将看到我们可以配置其他选项来控制重建索引的过程。

The below window shows the Rebuild Index maintenance task configuration window with the additional enhancements outlined by the red boxes:

下面的窗口显示“ 重建索引维护”任务配置窗口，并用红色框概述了其他增强功能：

It is clear from the previous Rebuild Index configuration window that the new enhancements are performed on both the Advanced Options and the Index Stats Options sections. In the Advanced Options section, the included options are:

从先前的“重建索引”配置窗口可以清楚地看到，“高级选项”和“索引统计信息选项”部分都进行了新的增强。 在“ 高级选项”部分中，包括的选项是：

• The Pad Index checkbox, that is used to make sure that the fillfactor value specified for the indexes will also be applied to the intermediate level pages of the index, not only the leaf level pages.

  填充索引复选框，用于确保为索引指定的填充因子值也将应用于索引的中间级别页面，而不仅是叶子级别页面。

• The MAXDOP checkbox is used to specify the number of CPUs that are used to rebuild the index, overriding the default Maximum Degree Of Parallelism value at the server level. This option will work only in the Enterprise and Developer SQL Server Editions.

  MAXDOP复选框用于指定用于重建索引的CPU数量，从而覆盖服务器级别的默认“最大并行度”值。 此选项仅在企业版和开发人员SQL Server版中有效。

• The Low Priority Used checkbox is used to specify if the online index rebuild process will wait in the Low Priority queue, the new feature introduced in SQL Server 2014 version to control how the blocking mechanism, that is required by the online index rebuild operation in the Enterprise SQL Server Edition, will be handled. This new feature provides you with two control parameters; the MAX DURATION, in which you can specify the time, in minutes, that the online index rebuild process will wait in the low priority queue. If the online index rebuild process exceeds that time, the Abort After Wait parameter can be used to specify the action that will be performed after exceeding that time. The Abort After Wait parameter has three options,
  • NONE in which the online index process will continue working after exceeding the specified time,
  • SELF in which the online index process will be killed after exceeding the specified time,
  • or BLOCKERS in which the online index rebuild process will kill all transactions that are blocking it from being processed.

  “ 使用的低优先级”复选框用于指定在线索引重建过程是否将在“ 低优先级”队列中等待 ，这是SQL Server 2014版本引入的新功能，用于控制在线索引重建操作中所需的阻止机制。将处理Enterprise SQL Server Edition。 这项新功能为您提供了两个控制参数。 MAX DURATION ，您可以在其中指定以分钟为单位的在线索引重建过程将在低优先级队列中等待的时间。 如果联机索引重建过程超过了该时间，则可以使用“ 等待后中止”参数来指定超过该时间后将要执行的操作。 “等待后中止”参数具有三个选项，

  • NONE ，超过指定时间后，在线索引过程将继续工作，
  • SELF ，超过指定时间后将终止在线索引过程，
  • 或BLOCKERS ，其中联机索引重建过程将杀死所有阻止对其进行处理的事务。

The Index Stats Options section provides us with new parameters that helps in specifying which indexes will be rebuilt and the indexes that will be excluded from the rebuild process, based on the index statistics criteria using the sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats system DMVs internally. From this section you can control:

“ 索引统计信息选项”部分为我们提供了新的参数，这些参数可根据内部使用sys.dm_db_index_usage_stats和sys.dm_db_index_physical_stats系统DMV的索引统计标准，帮助指定将重建的索引和将被排除在重建过程之外的索引。 在此部分中，您可以控制：

• The Scan Type, that controls how much precession is required for the index statistics, taking into consideration the amount of resources that will be consumed in gathering this specified amount of index statistics. The Scan Type can take one of the three options; Fast, Sampled or Details statistics gathering.

  扫描类型 ，它控制索引统计信息需要多少进动，同时考虑到收集此指定数量的索引统计信息将消耗的资源量。 扫描类型可以采用以下三个选项之一： 快速 ， 采样或详细信息统计信息收集。

• The Optimize Index Only If, that is used to tune the filters that control which indexes will be refreshed and the indexes that will be excluded. You can specify the Fragmentation percentage threshold, the Page Count threshold and the Used in Last (days) threshold for the indexes that will be rebuilt.

  仅限优化索引 ，用于调整用于控制哪些索引将被刷新以及哪些索引将被排除的过滤器。 您可以为要重建的索引指定“ 碎片百分比”阈值，“ 页数”阈值和“ 最近使用（天）”阈值。

The below window shows the Reorganize Index maintenance task configuration window with the additional enhancements outlined by the red box:

以下窗口显示“ 重新组织索引维护”任务配置窗口，并以红色框概述了其他增强功能：

The Reorganize Index task is always single thread and online in all SQL Server editions so that we need to specify only the candidate indexes that should be refreshed. The Index Stats Options section in the Define Reorganize Index Task window provides us also with new parameters that help in specifying which indexes will be reorganized and the indexes that will be excluded from the reorganize process, based on the index statistics. Just in the same as the previously described parameters in the Rebuild Index task work, from this section you can control:

在所有SQL Server版本中，“重新组织索引”任务始终是单线程且处于联机状态，因此我们仅需要指定应刷新的候选索引。 “定义重新组织索引任务”窗口中的“ 索引统计信息选项”部分还为我们提供了新的参数，这些参数可帮助您根据索引统计信息指定要重新组织哪些索引以及将哪些索引从重新组织过程中排除。 与“重建索引”任务中先前描述的参数相同，您可以从此部分控制：

• The Scan Type, that can take one of the three options; Fast, Sampled or Details statistics gathering.

  扫描类型 ，可以采用以下三个选项之一： 快速 ， 采样或详细信息统计信息收集。

• The Optimize Index Only If, that can specify the Fragmentation percentage threshold, the Page Count threshold and the Used in Last (days) threshold for the indexes that will be rebuilt.

  仅当优化索引时 ，可以为要重建的索引指定碎片百分比阈值， 页面计数阈值和最近使用天数阈值。

数据库检查完整性任务 (Database Check Integrity Task)

The Check Database Integrity task performs a check on the logical and physical allocation and structural integrity of all database objects, with the ability to check the integrity of a single database or multiple databases, and whether to check the database indexes too, by running the DBCC CHECKDB T-SQL command. In this way, it will make sure that there is no integrity issues or corruption at the database pages’ level.

“检查数据库完整性”任务对所有数据库对象的逻辑和物理分配以及结构完整性进行检查，并具有通过运行DBCC来检查单个数据库或多个数据库的完整性以及是否也检查数据库索引的能力。 CHECKDB T-SQL命令。 这样，可以确保在数据库页面级别上没有完整性问题或损坏。

The following window shows the Database Check Integrity maintenance task configuration window with the additional enhancements outlined by the red box:

以下窗口显示“ 数据库检查完整性”维护任务配置窗口，并用红色框概述了其他增强功能：

Starting from SQL Server 2016, the Database Check Integrity task configuration window includes the below three additional control parameters:

从SQL Server 2016开始，数据库检查完整性任务配置窗口包括以下三个其他控制参数：

• The Physical Only checkbox, that is used to limit the integrity check to be performed only at the pages’ physical structure, record headers and the database allocation consistency levels only. This limitation helps us in reducing the time and resources required to perform the DBCC CHECKDB command on the huge databases.

  仅物理复选框，用于将完整性检查限制为仅在页面的物理结构，记录标题和数据库分配一致性级别上执行。 此限制有助于我们减少在大型数据库上执行DBCC CHECKDB命令所需的时间和资源。

• The Tablock checkbox, that allows the DBCC CHECKDB command to obtain short-term exclusive locks on the database, instead of using an internal database snapshot, speeding up the DBCC CHECKDB command execution, but decreasing the database available concurrency.

  Tablock复选框，该复选框允许DBCC CHECKDB命令获取数据库上的短期互斥锁，而不是使用内部数据库快照，从而加快了DBCC CHECKDB命令的执行速度，但降低了数据库的并发性。

• The Max Degree Of Parallelism checkbox, that species the number of processors that will be used to run the DBCC CHECKDB command.

  “ 最大并行度”复选框，用于选择将用于运行DBCC CHECKDB命令的处理器数量。

结论 (Conclusion)

SQL Server Maintenance Plans are an old but good feature that helps us in automating large number of the most important database administration tasks. In SQL Server 2016, the number of enhancements are applied to the maintenance tasks that are responsible for rebuilding the database indexes, reorganizing the database indexes and checking the database integrity by providing more control to these tasks. In this article, we described these enhancements in details, where you can find how could you take benefits from these new options.

SQL Server维护计划是一个古老但不错的功能，可帮助我们自动执行大量最重要的数据库管理任务。 在SQL Server 2016中，增强功能的数量应用于维护任务，这些维护任务通过提供对这些任务的更多控制权来重建数据库索引，重新组织数据库索引以及检查数据库完整性。 在本文中，我们详细描述了这些增强功能，您可以在其中找到如何从这些新选项中受益。