AWS Cloud Operations Blog

Introducing the AWSSQLServer-Index Automation document for SQL Server maintenance

In the first and second posts in this five-part series, I introduced you to AWS Launch Wizard Systems Manager Automation documents for SQL Server and showed you how to run the AWSSQLServer-Backup and AWSSQLServer-Restore Automation documents.

In this post, I’ll show you how to use the AWSSQLServer-Index document for SQL Server index maintenance.

The SQL Server maintenance initiative is a joint venture between Launch Wizard and AWS Systems Manager to help ease the burden of maintenance operations on deployed SQL Server instances. These runbooks are implemented using a combination of SSM Automation documents and scripts that execute on your SQL Server instance. You can use the Systems Manager console or the AWS CLI to enter parameters and then execute the scripts on your SQL Server instance.

AWSSQLServer-Index

The AWSSQLServer-Index Automation document includes steps to perform index maintenance operations on a specified database. You can choose a configuration, which includes the actions to take based on the level of fragmentation.

Prerequisites for using this document

  • The instance must be running SQL Server.
  • An AWS Secrets Manager database credential must be configured.
  • You might need to grant additional permissions or assign roles to the instances that allow access to AWS Secrets Manager credentials.

This document uses fragmentation defaults of less than 5% for low, 5% to 30% for medium, and more than 30% for high.

Run AWSSQLServer-Index from the Systems Manager console

On the Documents page, choose the AWSSQLServer-Index document, and then choose Execute automation.

On the AWSSQLServer-Index details page, the Description tab is selected. The page includes Delete and Execute automation buttons and an Actions menu.

Figure 1: AWSSQLServer-Index

Under Input parameters, choose Show interactive instance picker, and then choose your SQL Server instance from the list.

Under Input parameters, the Show interactive instance picker option is selected. In the list of managed instances, ec2-us-sql-prod01 is selected.

Figure 2: Managed instances running SQL Server

Enter your parameters, and then choose Execute. In this case, I use the HighFragmentIndexAction field to indicate that the Automation document should perform an online reindex when it finds high fragmentation (more than 30%):

The HighFragmentIndexAction field is highlighted and Build index online is selected from the dropdown. For Database, tpcc is entered. For MediumFragmentIndexAction and LowFragmentIndexAction, Do nothing is selected from the dropdown. For SecretsManagerCredential, sqlsa is entered.

Figure 3: Parameters

I am going to target a database with known fragmentation. Figure 4 shows the MyFragmentedIndex table of the tpcc database is more than 90% fragmented:

The average fragmentation of MyFragmentedIndex is 99%. It has 1,144 fragments and pages.

Figure 4: MyFragmentedIndex

The console displays details for the executed steps. After one minute, the Automation document is executed successfully:

The Executed steps list displays columns for step ID, step number, step name, action, status, start time, and end time.

Figure 5: Executed steps

To view the output of each step, choose the execution ID link:

In the step output, the 99% fragmentation is highlighted.

Figure 6: Output

As you can see, the MyFragmentedIndex table with fragmentation of more than 50% was identified and reindexed.

Before After
The Before table shows average fragmentation of 99%. The After table shows average fragmentation of 1%.

Figure 7: Before and after

Summary

In this post, I showed how you can run the AWSSQLServer-Index document to reindex your SQL Server database.

In part 4, I’ll show you how to implement the AWSSQLServer-DBCC document to execute database consistency checks (DBCC) against your database.

In part 5, I’ll introduce you to Maintenance Windows, a powerful tool included with AWS Systems Manager for scheduling these actions against your SQL Server instances.

For more information about running SQL Server workloads on AWS, see Best practices for deploying Microsoft SQL Server on Amazon EC2. For more information about SSM Automation documents, see AWS Systems Manager Automation in the AWS Systems Manager User Guide.

To read the other posts in this series, see:

Available in These AWS Regions

The AWS Launch Wizard Systems Manager Automation documents for SQL Server are generally available. You can use them in the following AWS Regions:

US East (N. Virginia) US East (Ohio) US West (N. California)
US West (Oregon) Canada (Central) South America (Sao Paulo)
Asia Pacific (Mumbai) Asia Pacific (Singapore) Asia Pacific (Sydney)
Asia Pacific (Seoul) Asia Pacific (Tokyo) EU (Frankfurt)
EU (Ireland) EU (London) EU (Stockholm)

Support for the AWS Regions in China and for the GovCloud Region is in the works. There is no additional charge for using these Automation documents, only for the resources they create.

About the author

Alan Cranfield

Alan Cranfield

Alan Cranfield is a Senior Systems Engineer in the EC2 Windows team where he focuses on enabling and optimizing SQL Server workloads for AWS.