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

上次更新日期:2022 年 12 月 8 日

我想在我的 Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server 实例中创建索引重建维护任务。应如何操作?

简短描述

索引碎片是关键性问题,因为如果未能及时重建 SQL Server 数据库,其可能会导致 SQL Server 数据库出现性能问题。最佳实践是监控碎片级别并定期重建和重组索引。索引碎片以数据页间隙和逻辑碎片的形式出现。

注意:重建索引会删除并重新创建索引。根据索引类型和数据库引擎版本,可以离线或在线完成重建操作。重组会重构页面上的信息,而不是删除和重新创建索引。

RDS for SQL Server 没有可自动创建计划和作业以重建或重组索引的维护计划。但是,您可以使用以下方法来重建索引:

  • 方法 1:创建 SQL Server 代理作业以重建索引和更新统计信息。
  • 方法 2:使用脚本手动创建重建作业,并根据需要进行计划。

解决方案

方法 1:创建 SQL Server Agent 作业以重建索引和更新统计信息

1.    启动 Microsoft SQL Server Management Studio 客户端,然后登录。

2.    在右侧面板上,右键单击 SQL Server Agent(SQL Server 代理)。然后,选择 New(新建)、Job(作业)以创建 SQL 代理作业。

3.    输入代理作业的 Name(名称)和 Description(描述),然后选择OK(确定)。

示例:

  • Name(名称):Indexrebuild_job
  • Description(描述):用于索引重建的 RDS for SQL 服务器代理作业。

4.    选择 Steps(步骤),然后选择 New(新建)以添加执行步骤。此时会显示新窗口。

5.    输入步骤名称

6.    选择 Database(数据库),然后添加要定期运行的命令。

以下是索引重建 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.    选择 OK(确定)。

8.    选择 Schedules(计划),然后选择 New(新建),添加何时运行索引重建作业的计划。

9.    输入计划 Name(名称)、Schedule type(计划类型)等类似字段,然后选择 OK(确定)。

10.    查看您刚刚创建的作业并右键单击它。然后,选择 Start Job at Step(在步骤中启动作业)以手动运行该作业,验证其是否可以正常运行。

注意:在部署到生产 RDS 数据库之前,请在开发 RDS 数据库中测试本示例中提供的脚本。索引重建时间因索引的大小和数量而有很大差异。

优化器必须取得涉及表列键值分布(统计数据)的最新信息,才能生成最佳执行计划。最佳实践是定期更新所有表的统计数据。避免在重建索引的日期更新统计数据。

请记住,一次只能在一张表上更新统计数据。数据库级命令 sp_updatestats 在 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 代理作业只能由当前登录的账户创建。不允许其他账户作为作业所有者。


这篇文章对您有帮助吗?


您是否需要账单或技术支持?