維護計畫Maintenance Plans

適用於: 是SQL Server否Azure SQL Database否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 Agent 作業執行。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 Agent 作業。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
設定 Agent XPs 伺服器組態選項,以啟用 SQL Server Agent 擴充預存程序。Configure the Agent XPs server configuration option to enable the SQL Server Agent extended stored procedures. Agent XPs 伺服器組態選項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)