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

簡短描述

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

**注意：**重建索引會捨棄然後重新建立該索引。根據索引類型和資料庫引擎版本，重建作業可以離線或線上完成。重新組織會重新架構頁面上的資訊，而不是捨棄然後重新建立索引。

RDS for SQL Server 沒有可自動建立計劃和任務來重建或重新組織索引的維護計劃。不過，您可以使用下列方法來重建索引：

• 方法 1： 建立 SQL Server 代理程式任務以重建索引並更新統計資料。
• 方法 2： 使用指令碼手動建立重建任務，並視需要排程。

解決方法

方法 1： 建立 SQL Server 代理程式任務以重建索引並更新統計資料

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

2.    在右側面板中，在 SQL Server 代理程式上按一下滑鼠右鍵。然後，依次選擇新增、任務，以建立 SQL 代理程式任務。

3.    輸入代理程式任務的名稱和描述，然後選取確定。

範例：

• 名稱： Indexrebuild_job
• **描述：**用於索引重建的 RDS for SQL 伺服器代理程式任務。

4.    選取步驟，然後選取新增以新增執行步驟。會出現一個新視窗。

5.    輸入步驟名稱。

6.    選取資料庫，然後新增您要定期執行的命令。

以下是索引重建 SQL 命令的範例。您可以使用此範例命令，重建指定資料庫中超過 30% 片段之所有資料表的索引片段。將第一行中 [DBNAME] 的值變更為資料庫的正確名稱。如果您要為所有資料庫執行相同的 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

7.    選取確定。

8.    選取排程，然後選擇新增以新增排程，確定何時執行索引重建任務。

9.    輸入排程名稱、排程類型和類似欄位，然後選取確定。

10.    檢視您剛建立的任務，然後按一下滑鼠右鍵。接下來，選擇逐步啟動任務以手動執行任務，確認該任務可以正常執行。

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

最佳化程式必須擁有資料表資料欄之索引鍵值 (統計資料) 分配的最新資訊，才能產生最佳的執行計劃。最佳實務是定期更新所有資料表的統計資料。避免在重建索引的日期更新統計資料。

請記住，一次只能在一個資料表上更新統計資料。資料庫層級命令 sp_updatestats (來自 Microsoft 網站) 不適用於 Amazon RDS。使用更新統計資料寫入指標，以更新資料庫中所有物件的統計資料。或者，圍繞 sp_updatestats 建置一個包裝函式並對其排程。

若要使用圍繞 sp_updatestats 的包裝函式，請執行下列動作：

1.    執行命令以建立儲存的程序：

create procedure myRDS_updatestats
with execute as ‘dbo’
as
exec sp_updatestats
go

2.    在新程序上將執行許可授予使用者：

grant execute on myRDS_updatestats to user

3.    遵循方法 1 中的前述步驟來排程更新統計資料任務。

方法 2： 使用指令碼手動建立重建任務，並視需要排程

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

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

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

CreateDatabaseAndObjects.sql 指令碼會執行下列動作：

• 建立名為 ServerDatabases 的資料表。此資料表會記錄目前執行個體上的資料庫。系統資料庫 (主要資料庫、模型資料庫、TempDB 和 msdb) 會排除在外。由 SSIS 和 SSRS 等 SQL Server 元件建立的資料庫 (rdsadmin_ReportServer、rdsadmin_ReportServerTempDB) 也會排除在外。
• 建立名為 ServerTables 的資料表。此資料表會收集 ServerDatabases 資料表中所有資料庫的資料表。
• 建立名為訊息的資料表。此資料表包含針對索引執行的訊息 (REBUILD 或 REORGANIZE)。如果需要，您可以複製訊息並手動執行。

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 指令碼，請先複製並執行 CreateDatabaseAndObjects.sql。然後，執行 CreateWeeklyMaintenanceJob.sql 指令碼。

**注意：**由於受管服務限制，SQL Server 代理程式任務只能由目前登入的帳戶建立。不允許其他帳戶作為任務擁有者。