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

Short description

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

Amazon RDS for SQL Server doesn't have maintenance plans that automatically create plans or jobs to rebuild or reorganize indexes.

However, you can create a SQL Server Agent job to rebuild indexes and update statistics. Or, use scripts and schedules to manually create a rebuild job.

When you rebuild an index, the database engine drops and recreates the index. Depending on the type of index and the database engine version, you can create a rebuild operation offline or online. When you reorganize an index, the database engine doesn't drop or recreate the index. Instead, the database engine restructures the information on the pages.

Resolution

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

Rebuild indexes

Complete the following steps:

1. Start the Microsoft SQL Server Management Studio (SSMS) client, and then connect to your RDS for SQL Server DB instance.
2. On the right pane, right-click SQL Server Agent, and then choose New Job.
3. For Name, enter a name for the agent job. For Description, enter a description.
4. Choose OK.
5. Choose Steps, and then choose New.
6. For Step name, enter a name for the step.
7. Choose Database, and then add the command that you want to periodically run.
   The following is an example index rebuild SQL command:

   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 > 10
     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';
       -- Add WITH (ONLINE = ON) only for Enterprise Edition:
       -- SET @command = @command + N' WITH (ONLINE = ON)';
     END
     ELSE IF @avg_fragment >= 10.0
     BEGIN
       SET @command = N'ALTER INDEX ' + @ixname + N' ON ' + @schemaname + N'.' + @tablename + N' REORGANIZE';
     END
     EXEC(@command)
     FETCH NEXT FROM AWS_Cusrsor INTO @Objectid, @Indexid, @schemaname, @tablename, @ixname, @avg_fragment
   END
   
   CLOSE AWS_Cusrsor
   DEALLOCATE AWS_Cusrsor

   Note: Replace DBNAME with the name of your database. The script reorganizes indexes that exceed 10% fragmentation and rebuilds indexes that exceed 30% fragmentation in the specified database. If you run the same SQL command for all databases, then modify the command accordingly, or create a separate job for each database. You can only use the WITH (ONLINE = ON) clause on Enterprise Edition. For Standard Edition, remove this clause or use REORGANIZE instead.
8. Choose OK.
9. Choose Schedules, and then choose New to add a schedule for when to run the index rebuild job.
10. Enter the details for your schedule, and then choose OK.
11. Right-click the job, and then choose Start Job at Step to verify that the job can run.

Note: Before you deploy the index to a production database, test the scripts in the preceding example in a development RDS database. The index rebuild time varies based on the size and number of indexes.

Update statistics

To generate optimal run plans, the optimizer must have up-to-date information on the distribution of key values (statistics) of table columns. It's a best practice to regularly update the statistics for all tables. Don't update statistics on the days that you rebuild indexes.

Update statistics works on one table at a time. The sp_updatestats database-level command isn't available in Amazon RDS. For more information, see sp_updatestats.

To update statistics on all the objects in a database, use update statistics to write a cursor. Or, build a wrapper around sp_updatestats and schedule it.

To use a wrapper around sp_updatestats, complete the following steps:

1. Run the following 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. Schedule update stat jobs.

Use scripts and schedules to manually create rebuild jobs

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

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

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

The CreateDatabaseAndObjects.sql script creates the following tables:

• A Messages table that contains the message for the REBUILD or REORGANIZE action that occurs on the index.
  Note: You can copy and manually run the message, if needed.
• A ServerDatabases table that records the databases on the current instance.
  Note: The table excludes system databases (Master, Model, TempDB, and msdb). The table also excludes databases that SQL Server components create, such as SSIS and SSRS (rdsadmin_ReportServer and rdsadmin_ReportServerTempDB).
• A ServerTables table that collects tables for all databases in the ServerDatabases table.

The CreateWeeklyMaintenanceJob.sql script creates the following stored procedures:

• The sp_PopulateDatabases procedure records all databases on the instance in the ServerDatabases table.
  Note: The table excludes system databases or databases that SQL Server components create, such as SSAS and SSRS. The table includes SSIDB for SSIS.
• The sp_PopulateTables procedure records each database's tables into ServerTables.
  Note: After the procedure records the tables, it checks the schema that the table belongs to, and then searches for indexes that the schema has. The procedure then searches the indexes for the most fragmented index information and records it.
• The sp_ReindexTables procedure reads the information from ServerTables and uses the following rules to start the rebuild or defragmentation process:
  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, complete the following steps:

1. Copy and run the CreateDatabaseAndObjects.sql script.
2. Run the CreateWeeklyMaintenanceJob.sql script.

Note: Because of managed service restrictions, you can create SQL Server Agent jobs only in an AWS account that's logged in to the SSMS client.

Related information

Introducing the AWSSQLServer-Index Automation document for SQL Server maintenance

Best practices for configuring performance parameters for Amazon RDS for SQL Server