如何建立維護任務以在 Amazon RDS for SQL Server 執行個體中重建索引？

簡短描述

索引分割是一個嚴重問題，如果不及時重建，可能會導致 SQL Server 資料庫的效能問題。最佳做法是監控分割程度，並定期重建和重新組織索引。索引分割發生在資料頁面和邏輯分割中的間隙。

Amazon RDS for SQL Server 沒有可自動建立計劃和作業來重建或重新組織索引的維護計劃。

但是，您可以使用下列任一方法來重建索引：

• 建立 SQL Server 代理程式作業來重建索引，並更新統計資料。
• 使用指令碼和排程手動建立重建作業。

**注意：**重建索引時，資料庫引擎會刪除並重新建立索引。根據索引類型和資料庫引擎版本，您可以透過離線或線上方式重建作業。重新組織索引時，資料庫引擎不會刪除或重新建立索引。相反，資料庫引擎會重新構建頁面上的資訊。

解決方法

建立 SQL Server 代理程式作業來重建索引，並更新統計資料

請完成下列步驟：

1. 啟動 Microsoft SQL Server Management Studio (SSMS) 用戶端，然後登入。

2. 在右窗格上，以滑鼠右鍵按一下 SQL Server Agent (SQL Server 代理程式)，然後選擇 New Job (新建作業)。

3. 在 Name (名稱) 中，輸入代理程式作業的名稱，並在 Description (描述) 中輸入描述。

4. 選擇 OK (確定)。

5. 選擇 Steps (步驟)，然後選擇 New (新建)。

6. 在 Step name (步驟名稱) 中，輸入步驟的名稱。

7. 選取 Database (資料庫)，然後新增您要定期執行的命令。
   以下是索引重建 SQL 命令的範例：

   Use [DBNAME]SET NOCOUNT ON  
   DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fragment float,@command VARCHAR(4000)  
   DECLARE AWS_Cusrsor CURSOR FOR  
   SELECT A.object_id,A.index_id,QUOTENAME(SS.NAME) AS schemaname,QUOTENAME(OBJECT_NAME(B.object_id,B.database_id))as tablename ,QUOTENAME(A.name) AS ixname,B.avg_fragmentation_in_percent AS avg_fragment FROM sys.indexes A inner join sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') AS B  
   ON A.object_id=B.object_id and A.index_id=B.index_id  
   INNER JOIN SYS.OBJECTS OS ON A.object_id=OS.object_id  
   INNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_id  
   WHERE B.avg_fragmentation_in_percent>30  AND A.index_id>0 AND A.IS_DISABLED<>1  
   ORDER BY tablename,ixname  
   OPEN AWS_Cusrsor  
   FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment  
   WHILE @@FETCH_STATUS=0  
   BEGIN  
   IF @avg_fragment>=30.0  
   BEGIN  
   SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';  
   --Can add following line for index reorganization. Else remove following line.  
   SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REORGANIZE';  
   END  
   --PRINT @command  
   EXEC(@command)  
   FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment  
   END  
   CLOSE AWS_Cusrsor  
   DEALLOCATE AWS_Cusrsor

   **注意：**將 DBNAME 替換為您資料庫的名稱。您可以使用上述範例命令，重建指定資料庫中超過 30% 分割之所有資料表的索引分割。如果您要為所有資料庫執行相同的 SQL 命令，則請相應地修改命令，或為每個資料庫建立單獨的作業。

8. 選擇 OK (確定)。

9. 選擇 Schedules (排程)，然後選擇 New (新增) 以新增排程，確定何時執行索引重建作業。

10. 輸入排程的詳細資訊，然後選擇 OK (確定)。

11. 在作業上按一下右鍵，然後選擇 Start Job at Step (逐步啟動作業) 以確認作業是否可執行。

**注意：**在將索引部署至生產資料庫之前，請在開發 RDS 資料庫中測試上述範例中的指令碼。索引重建時間會根據索引的大小和數目而有所區別。

若要產生最佳執行計劃，最佳化程式必須擁有資料表資料欄之索引鍵值 (統計資料) 分配的最新資訊。最佳做法是定期更新所有資料表的統計資料。請勿在重建索引的當天更新統計資料。

一次只能在一個資料表上更新統計資料。sp_updatestats 資料庫層級命令在 Amazon RDS 中無法使用。如需詳細資訊，請參閱 Microsoft 網站上的 sp_updatestats。

若要更新資料庫中所有物件的統計資料，請使用更新統計資料寫入游標。或者，圍繞 sp_updatestats 建置一個包裝函式並對其排程。

若要使用圍繞 sp_updatestats 的包裝函式，請完成以下步驟：

1. 執行以下命令來建立預存程序：

   create procedure myRDS_updatestatswith execute as ‘dbo’  
   as  
   exec sp_updatestats  
   go

2. 授予使用者對新程序的執行權限：

   grant execute on myRDS_updatestats to user

3. 排程更新統計作業。

使用指令碼和排程手動建立重建作業

若要檢查分割的索引，並按照排程對其們執行索引重建，請手動建立指令碼或程序。您可以使用指令碼建立自己的程式碼，並設定手動維護作業。

您也可以使用 GitHub 網站上的 aws-rds-indexmaintenance-job-example SQL 指令碼。該指令碼會根據分割層級，每週重建和重新組織索引。指令碼會建立資料庫 (IndexStats) 和物件 (資料表)，以儲存執行個體上所有資料庫的相關資訊，包括資料庫的資料表、索引和分割百分比。

aws-rds-indexmaintenance-job-example 包含 CreateDatabaseAndObjects.sql 和 CreateWeeklyMaintenanceJob.sql 指令碼。

CreateDatabaseAndObjects.sql 指令碼會建立下列資料表：

• Messages 資料表，其中包含在索引上發生之重建或重新組織動作的訊息。
  **注意：**如果需要，您可以複製並手動執行訊息。
• ServerDatabases 資料表，其記錄目前執行個體上的資料庫。
  **注意：**該資料表不包括系統資料庫 (Master、Model、TempDB 和 msdb)。該資料表也排除了 SQL Server 元件所建立的資料庫，例如 SSIS 和 SSRS= (rdsadmin_ReportServer 和 rdsadmin_ReportServerTempDB)。
• ServerTables 資料表收集了 ServerDatabases 資料表中所有資料庫的資料表。

CreateWeeklyMaintenanceJob.sql 指令碼會建立下列預存程序：

• sp_PopulateDatabases 程序會在 ServerDatabases 資料表中記錄執行個體上的所有資料庫。
  **注意：**該資料表不包括系統資料庫或 SQL Server 元件建立的資料庫，例如 SSAS 和 SSRS。該資料表包括 SSIS 的 SSIDB。
• sp_PopulateTables 程序會將每個資料庫的資料表記錄到 ServerTables 中。
  **注意：**程序記錄資料表之後，會檢查資料表所屬的結構描述，然後搜尋結構描述所擁有的索引。然後，該程序會在索引中搜尋最分散的索引資訊並記錄下來。
• **sp_ReindexTables **程序會從 ServerTables 讀取資訊，並使用下列規則啟動重建或重組程序：
  分割程度為 0-9％ = NOTHING
  分割程度為 10-30% = REORGANIZE
  分割程度為 31-100% = REBUILD

若要使用 GitHub 上的 aws-rds-indexmaintenance-job-example 指令碼，請完成以下步驟：

1. 複製並執行 CreateDatabaseAndObjects.sql 指令碼。
2. 執行 CreateWeeklyMaintenanceJob.sql 指令碼。

**注意：**由於受管服務限制，您只能在登入 SSMS 用戶端的 AWS 帳戶中建立 SQL Server 代理程式作業。