维护计划Maintenance Plans

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

维护计划可创建所需的任务工作流,以确保优化数据库、定期进行备份并确保数据库一致。Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies. 维护计划向导还可创建核心维护计划,但手动创建计划具有更大的灵活性。The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility.

维护计划的优点Benefits of Maintenance Plans

SQL Server 2019 数据库引擎SQL Server 2019 Database Engine中,维护计划将创建由 Integration ServicesIntegration Services 代理作业运行的 SQL ServerSQL Server 包。In SQL Server 2019 数据库引擎SQL Server 2019 Database Engine, maintenance plans create an Integration ServicesIntegration Services package, which is run by a SQL ServerSQL Server Agent job. 可以按预订的时间间隔手动或自动运行维护计划。Maintenance plans can be run manually or automatically at scheduled intervals.

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 维护计划可提供以下功能:maintenance plans provide the following features:

  • 使用各种典型维护任务创建工作流的功能。Workflow creation using a variety of typical maintenance tasks. 此外,还可以创建自己的 Transact-SQLTransact-SQL 脚本。You can also create your own custom Transact-SQLTransact-SQL scripts.

  • 概念性层次结构。Conceptual hierarchies. 使用每个计划可创建或编辑任务工作流。Each plan lets you create or edit task workflows. 每个计划的任务可分组到子计划中,可以安排这些子计划在不同时间运行。Tasks in each plan can be grouped into subplans, which can be scheduled to run at different times.

  • 支持可以用于主服务器/目标服务器环境中的多服务器计划。Support for multiserver plans that can be used in master server/target server environments.

  • 支持在远程服务器上记录计划历史记录。Support for logging plan history to remote servers.

  • 支持 Windows 身份验证和 SQL ServerSQL Server 身份验证。Support for Windows Authentication and SQL ServerSQL Server Authentication. 请尽可能使用 Windows 身份验证。When possible, use Windows authentication.

维护计划功能Maintenance Plan Functionality

可以创建维护计划来执行以下任务:Maintenance plans can be created to perform the following tasks:

  • 用新填充因子重新生成索引来重新组织数据和索引页上的数据。Reorganize the data on the data and index pages by rebuilding indexes with a new fill factor. 用新填充因子重新生成索引会确保数据库页中包含的数据量和可用空间的平均分布。Rebuilding indexes with a new fill factor makes sure that database pages contain an equally distributed amount of data and free space. 还使得以后能够更快地增长。It also enables faster growth in the future. 有关详细信息,请参阅 为索引指定填充因子For more information, see Specify Fill Factor for an Index.

  • 通过删除空数据库页压缩数据文件。Compress data files by removing empty database pages.

  • 更新索引统计信息,确保查询优化器含有关于表中数据值分布的最新信息。Update index statistics to make sure the query optimizer has current information about the distribution of data values in the tables. 这使得查询优化器能够更好地确定访问数据的最佳方法,因为可以获得数据库中存储数据的详细信息。This enables the query optimizer to make better judgments about the best way to access data, because it has more information about the data stored in the database. 虽然 SQL ServerSQL Server 会定期自动更新索引统计信息,但是此选项可以对统计信息立即进行强制更新。Although index statistics are automatically updated by SQL ServerSQL Server periodically, this option can force the statistics to update immediately.

  • 对数据库内的数据和数据页执行内部一致性检查,确保系统或软件故障没有损坏数据。Perform internal consistency checks of the data and data pages within the database to make sure that a system or software problem has not damaged data.

  • 备份数据库和事务日志文件。Back up the database and transaction log files. 数据库和日志备份可以保留一段指定时间。Database and log backups can be retained for a specified period. 这样,您就可以为备份创建一份历史记录,以便在需要将数据库还原到早于上一次数据库备份的时间的时候使用。This lets you create a history of backups to be used if you have to restore the database to a time earlier than the last database backup. 还可以执行差异备份。You can also perform differential backups.

  • 运行 SQL ServerSQL Server 代理作业。Run SQL ServerSQL Server Agent jobs. 这可以用来创建可执行各种操作的作业以及运行这些作业的维护计划。This can be used to create jobs that perform a variety of actions and the maintenance plans to run those jobs.

维护任务生成的结果可以作为报表写入文本文件,或写入 msdb 维护计划表(sysmaintplan_logsysmaintplan_logdetail)。The results generated by the maintenance tasks can be written as a report to a text file or to the maintenance plan tables (sysmaintplan_log and sysmaintplan_logdetail) in msdb. 若要在日志文件查看器中查看结果,请右键单击“维护计划” ,再单击“查看历史记录” 。To view the results in the log file viewer, right-click Maintenance Plans, and then click View History.

参考以下主题以开始使用维护计划。Use the following topics to get started with maintenance plans.

说明Description 主题Topic
配置“代理 XP” 服务器配置选项以启用 SQL Server 代理扩展存储过程。Configure the Agent XPs server configuration option to enable the SQL Server Agent extended stored procedures. “代理 XP”服务器配置选项Agent XPs Server Configuration Option
说明如何使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL 创建维护计划。Describes how to create a maintenance plan by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 创建维护计划Create a Maintenance Plan
说明如何使用维护计划设计图面创建维护计划。Describes how to create a maintenance plan by using the Maintenance Plan Design Surface. 创建维护计划(维护计划设计图面)Create a Maintenance Plan (Maintenance Plan Design Surface)
对象资源管理器中可用的文档维护计划功能。Documents maintenance plan functionality available in Object Explorer. 维护计划节点(对象资源管理器)Maintenance Plans Node (Object Explorer)