如何在我的 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 表，用于记录当前实例上的数据库。
  **注意：**该表不包含系统数据库（主数据库、模型数据库、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% = 不执行操作
  碎片率为 10-30% = 重组
  碎片率为 31-100% = 重建

要使用 GitHub 上的 aws-rds-indexmaintenance-job-example 脚本，请完成以下步骤：

1. 复制并运行 CreateDatabaseAndObjects.sql 脚本。
2. 运行 CreateWeeklyMaintenanceJob.sql 脚本。

**注意：**由于托管服务限制，您只能在登录到 SSMS 客户端的 AWS 账户中创建 SQL Server 代理作业。