How do I create maintenance tasks to rebuild indexes in my RDS for SQL Server instance?

6 minute read
0

I want to create index rebuild maintenance tasks in my Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server instance.

Short description

Index fragmentation is a critical issue that can cause performance problems in SQL Server databases if they aren't rebuilt promptly. It's a best practice to monitor the level of fragmentation and rebuild and reorganize indexes regularly. Index fragmentation occurs as gaps in data pages and logical fragmentation.

Note: Rebuilding an index drops and then re-creates the index. Depending on the type of index and the database engine version, a rebuild operation can be done offline or online. Reorganize restructures the information on the pages instead of dropping and then re-creating the indexes.

RDS for SQL Server doesn't have maintenance plans that automatically create plans and jobs to rebuild or reorganize indexes. However, you can use the following methods to rebuild your indexes:

  • Method 1: Create a SQL Server agent job to rebuild indexes and update statistics.
  • Method 2: Manually create rebuild job using scripts and scheduled as needed.

Resolution

Method 1: Create a SQL Server Agent job to rebuild indexes and update statistics

1.    Start the Microsoft SQL Server Management Studio client, and then log in to it.

2.    On the right panel, right-click SQL Server Agent. Then, choose New, Job to create a SQL agent job.

3.    Enter a Name and Description for the agent job, and then select OK.

Example:

  • Name: Indexrebuild_job
  • Description: RDS for SQL server agent job for index rebuild.

4.    Select Steps, and then select New to add an execution step. A new window appears.

5.    Enter the Step name.

6.    Select Database, and then add the command that you want to run periodically.

The following is an example index rebuild SQL command. You can use this example command to rebuild fragmented indexes of all tables in the specified DB that exceed 30% fragmentation. Change the value of [DBNAME] in the first line to the correct name for your database. If you run the same SQL command for all databases, then modify the command accordingly or create a separate job for each database.

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.    Select OK.

8.    Select Schedules, and then choose New to add a schedule for when to run the index rebuild job.

9.    Enter the schedule Name, Schedule type, and similar fields, and then select OK.

10.    View the job that you just created and right-click it. Then, choose Start Job at Step to manually run the job to verify that the job can run properly.

Note: Test the scripts provided in this example in a development RDS database before deploying to a production RDS database. The index rebuild time varies greatly based on size and number of indexes.

The optimizer must have up-to-date information on the distribution of key values (statistics) of table columns to generate optimal execution plans. It's a best practice to update the statistics for all tables on a regular basis. Avoid updating statistics on the days that you’re rebuilding indexes.

Keep in mind that the update statistics work on one table at a time. The database level command sp_updatestats (on the Microsoft website) isn't available in Amazon RDS. Write a cursor using update statistics to update statistics on all the objects in a database. Or, build a wrapper around sp_updatestats and schedule it.

To use a wrapper around sp_updatestats, do the following:

1.    Run the command to create a stored procedure:

create procedure myRDS_updatestats
with execute as ‘dbo’
as
exec sp_updatestats
go

2.    Grant execute permission to a user on the new procedure:

grant execute on myRDS_updatestats to user

3.    Follow the preceding steps from Method 1 to schedule update stat jobs.

Method 2: Manually create rebuild jobs using scripts and schedule as needed

You can manually create scripts or procedures to check fragmented indexes and run index rebuild against them on a schedule. You can create your own code and configure manual maintenance jobs using scripts.

You can also use the aws-rds-indexmaintenance-job-example SQL scripts from GitHub. These scripts rebuild and reorganize indexes weekly depending on the fragmentation level. The script creates a database (IndexStats) and objects (tables) to store information regarding all databases on the instance. This information includes the databases' tables, indexes, and fragmentation percentages.

The aws-rds-indexmaintenance-job-example contains two scripts, CreateDatabaseAndObjects.sql and CreateWeeklyMaintenanceJob.sql.

The CreateDatabaseAndObjects.sql script does the following:

  • Creates a table named ServerDatabases. This table records the databases on the current instance. System databases (Master, Model, TempDB, and msdb) are excluded. Databases created by SQL Server components such as SSIS and SSRS (rdsadmin_ReportServer, rdsadmin_ReportServerTempDB) are also excluded.
  • Creates a table named ServerTables. This table collects tables for all databases in the ServerDatabases table.
  • Creates a table named Messages. This table contains the message that was acted upon on the index (REBUILD or REORGANIZE). You can copy the message and run it manually, if needed.

The CreateWeeklyMaintenanceJob.sql script creates the following stored procedure:

  • sp_PopulateDatabases: This procedure goes through all of the databases on the instance and records them in the table ServerDatabases. It doesn't include system databases or databases created by SQL Server components such as SSAS and SSRS. SSIDB for SSIS is included.
  • sp_PopulateTables: This procedure goes through each database and records its tables into ServerTables. After recording the tables, it checks the schema the table belongs to, and then looks for any indexes it has. The stored procedure goes through the indexes and looks for the index information that's most fragmented and records it.
  • Sp_ReindexTables: This procedure reads the information from the ServerTables and starts the rebuild or defragmentation process using the following rules:
    Fragmentation is 0-9% = NOTHING
    Fragmentation is 10-30% = REORGANIZE
    Fragmentation is 31-100% = REBUILD

To use the aws-rds-indexmaintenance-job-example scripts from GitHub, copy and run the CreateDatabaseAndObjects.sql first. Then, run the CreateWeeklyMaintenanceJob.sql script.

Note: Because of managed service restrictions, SQL Server Agent jobs can be created only by the account that's currently logged in. No other accounts are allowed as job owners.