New user sign up using AWS Builder ID
New user sign up using AWS Builder ID is currently unavailable on re:Post. To sign up, please use the AWS Management Console instead.
如何建立維護任務以在 RDS for SQL Server 執行個體中重建索引?
我想在 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.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 代理程式任務只能由目前登入的帳戶建立。不允許其他帳戶作為任務擁有者。

相關內容
- 已提問 4 個月前lg...
- 已提問 2 年前lg...
- 已提問 2 年前lg...
- AWS 官方已更新 2 個月前
- AWS 官方已更新 2 年前
- AWS 官方已更新 2 個月前