How do I create maintenance tasks to rebuild indexes in my Amazon RDS for 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, 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 use either of the following methods to rebuild your indexes:

• Create a SQL Server agent job to rebuild indexes and update statistics.
• Use scripts and schedules to manually create a rebuild job.

Note: 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

Complete the following steps:

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

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, and 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>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

   Note: Replace DBNAME with the name of your database. You can use the preceding example command to rebuild fragmented indexes of all tables in the specified database that exceed 30% fragmentation. If you run the same SQL command for all databases, then modify the command accordingly, or create a separate job for each database.

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.

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're rebuilding indexes.

The update statistics work on one table at a time. The sp_updatestats database-level command isn't available in Amazon RDS. For more information, see sp_updatestats on the Microsoft website.

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_updatestatswith 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, including 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.