如何创建维护任务以在我的 RDS for SQL Server 实例中重建索引?

2 分钟阅读
0

我想在我的 Amazon Relational Database Service(Amazon RDS)for 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 Server 代理任务。

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 表中所有数据库的表。
  • 创建名为 Messages 的表。此表包含对索引执行操作(REBUILD 或 REORGANIZE)的消息。如果需要,您可以复制消息并手动运行它。

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 scripts,请先复制并运行 CreateDatabaseAndObjects.sql。然后运行 CreateWeeklyMaintenanceJob.sql 脚本。

**注意:**由于托管服务限制,SQL Server 代理任务只能由当前登录的帐户创建。不允许使用其他账户作为任务所有者。