SQL Server维护计划–好处，功能和特性

There is a range of functionalities that SQL Server Maintenance Plans offers, and these are designed in order to fulfill administration tasks either by using individual or appropriate combination of multiple functionalities:

SQL Server维护计划提供了一系列功能，这些功能旨在通过使用多个功能的单独或适当组合来完成管理任务：

• Rearrange the information in the data and index pages by rebuilding indexes with a specified fill factor. The fill factor is the option that provides fine-tuning of SQL Server index storage and performance
  通过使用指定的填充因子重建索引来重新排列数据和索引页中的信息。 填充因子是提供对SQL Server索引存储和性能进行微调的选项
• A fill factor value (the ReindexPercentage task property) determines the amount (in percentage) of index page that will be reserved for future index growth. If the new data is regularly added to the end of the table, there is no need to change the default value of the fill factor – 0. Otherwise, in order to improve the overall index performance, it’s advisable to specify the form fill factor that fits regular inserts and updates. But a word of caution – in case the fill factor value is too big (e.g. 50), the database performance can be 50% reduced
  填充因子值（ReindexPercentage任务属性）确定将为将来的索引增长保留的索引页的数量（百分比）。 如果将新数据定期添加到表的末尾，则无需更改填充因子的默认值–0。否则，为了提高整体索引性能，建议指定以下格式的填充因子：适合常规插入和更新。 但请注意，如果填充因子值太大（例如50），则数据库性能可能会降低50％
• Compress data files by removing empty database pages and returning them to the file system
  通过删除空的数据库页面并将其返回到文件系统来压缩数据文件
• Update index statistics is automatically performed. With this option, you can force immediate index statistics update in order to improve search performance for rapidly updated database
  更新索引统计信息将自动执行。 使用此选项，您可以强制立即进行索引统计信息更新，以提高快速更新的数据库的搜索性能。
• Perform database internal consistency checks in order to verify whether a software or system issue hasn’t corrupted the data
  执行数据库内部一致性检查，以验证软件或系统问题是否未破坏数据
• Back up the database and transaction log files in order to maintain the history of backups if a restore to a point in time is required
  如果需要还原到某个时间点，则备份数据库和事务日志文件以维护备份的历史记录
• Automatically run any defined Maintenance Plan task as a SQL Server Agent job using the Schedule option
  使用计划选项自动将任何已定义的维护计划任务作为SQL Server代理作业运行

There are two methods to create a SQL Server Maintenance Plan task – manual and via the Maintenance Plan Wizard option. The first one provides a wider range of options, properties, and flexibility during the creation, while the wizard offers a quick creation but with many of options and parameters (task properties) set to their defaults

有两种创建SQL Server维护计划任务的方法-手动和通过维护计划向导选项。 第一个在创建过程中提供了更广泛的选项，属性和灵活性，而向导提供了快速创建功能，但许多选项和参数（任务属性）设置为默认值

To manually create a Maintenance Plan task in SQL Server:

要在SQL Server中手动创建维护计划任务，请执行以下操作：

1. Object Explorer tree, select the 对象资源管理器”树中，从“维护计划”文件夹的上下文菜单中选择“ New Maintenance Plan option from the context menu of the Maintenance Plans folder新建维护计划”选项。

2. Using the Maintenance Plan Tasks toolbox, add the tasks you want to be performed at the scheduled time in desired order. To adjust the order of the task execution, use the arrows between the tasks as shown below

   使用维护计划任务工具箱，以所需的顺序添加要在计划的时间执行的任务。 要调整任务执行的顺序，请使用任务之间的箭头，如下所示

   

Each SQL Server maintenance plan consists of one or more subplans. The purpose of the subplans is to group tasks and provide different schedule for each subplan

每个SQL Server维护计划都包含一个或多个子计划。 子计划的目的是将任务分组并为每个子计划提供不同的计划

Once saved, the maintenance plan can be modified or executed ad-hoc, without waiting for its scheduled time. Each maintenance plan automatically creates the corresponding SQL Server Agent job that can be used for additional fine tuning of the plan execution – scheduling, SQL Server alerting, and notifying (whether the job failed or succeeded) via email, net send, or the Windows Application event log entry

保存后，可以立即修改或执行维护计划，而无需等待计划的时间。 每个维护计划都会自动创建相应SQL Server代理作业，该作业可用于对计划执行进行其他的微调-计划，SQL Server警报和通过电子邮件，网络发送或Windows应用程序通知（无论作业是否成功）事件日志条目

To use the wizard, in the Object Explorer tree, select the Maintenance Plan Wizard option from the context menu of the Maintenance Plans folder

若要使用向导，请在“ 对象资源管理器”树中，从“维护计划”文件夹的上下文菜单中选择“ 维护计划向导”选项。

Simply follow the click-and-point wizard dialog until the new SQL Server maintenance plan is created. This plan is editable the same way as manually created plans – after the wizard is closed, the created plan can be modified using the previously described designer window. The purpose of the wizard is to create appropriate tasks faster, with just a few clicks

只需遵循单击鼠标向导对话框，直到创建新SQL Server维护计划。 可以像手动创建计划一样编辑该计划–关闭向导后，可以使用前面介绍的设计器窗口来修改创建的计划。 该向导的目的是只需单击几下即可更快地创建适当的任务

However, either created manually or via the wizard, maintenance plans should be tested, by selecting the Execute option. Do not wait for the first scheduled execution to fail in order to fix plan settings omissions (e.g. the SQL Server and SQL Server Agent service accounts do not have full control of the backup directory)

但是，无论是手动创建还是通过向导创建，都应通过选择“执行”选项来测试维护计划。 不要等待第一个计划执行失败以修复计划设置遗漏（例如，SQL Server和SQL Server Agent服务帐户对备份目录没有完全控制权）

有关SQL Server维护计划任务和计划的建议做法 (Suggested practices on SQL Server Maintenance Plan tasks and scheduling)

There are several suggested practices which take maintenance task consequences into consideration. Maintenance task benefits depend on SQL Server usage and are not applicable for all scenarios

有几种建议的做法考虑了维护任务的后果。 维护任务的收益取决于SQL Server的使用情况，并不适用于所有情况

The Shrink Database task is not recommended to be frequently scheduled, as it may cause the database performance issues and both index and disk fragmentation

建议不要频繁安排“收缩数据库”任务，因为它可能会导致数据库性能问题以及索引和磁盘碎片

As for the index related tasks (rebuild, reorganize, and update statistics), they can also be the source of performance issues with significant resource (e.g. CPU and hard disk) overheads

对于与索引相关的任务（重建，重组和更新统计信息），它们也可能是性能问题的源头，并且存在大量资源（例如CPU和硬盘）的开销

It is recommended to create full database backups daily, but in this case, you must check for the available disk space and remove old backups on time

建议每天创建完整的数据库备份，但是在这种情况下，必须检查可用磁盘空间并按时删除旧备份

The database integrity check is a very resource intensive operation and it should be scheduled during off hours

数据库完整性检查是一项非常耗费资源的操作，应安排在下班时间进行

The practices and recommendations on specific maintenance tasks and scheduling are various and they depend on a SQL Server environment, usage, and company policy. The ultimate suggested practice is the one determined by a DBA, scheduled per needs and resource capabilities for a particular SQL Server.

有关特定维护任务和计划的实践和建议多种多样，并且取决于SQL Server环境，用法和公司策略。 最终建议的做法是由DBA根据特定SQL Server的需求和资源能力计划的做法。

翻译自: https://www.sqlshack.com/sql-server-maintenance-plans-benefits-features-functionalities/