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

2 分的閱讀內容
0

我想在 Amazon Relational Database Service (Amazon RDS) 中為 Microsoft 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.sqlCreateWeeklyMaintenanceJob.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 代理程式任務只能由目前登入的帳戶建立。不允許其他帳戶作為任務擁有者。