AWS Cloud Operations & Migrations 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.
Figure 1: AWSSQLServer-Index
Under Input parameters, choose Show interactive instance picker, and then choose your SQL Server instance from the list.
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%):
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:
Figure 4: MyFragmentedIndex
The console displays details for the executed steps. After one minute, the Automation document is executed successfully:
Figure 5: Executed steps
To view the output of each step, choose the execution ID link:
Figure 6: Output
As you can see, the MyFragmentedIndex table with fragmentation of more than 50% was identified and reindexed.
| Before | After |
 |
 |
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:
- Introducing the AWSSQLServer-Backup Automation document for SQL Server maintenance
- Introducing the AWSSQLServer-Restore Automation document for SQL Server maintenance
- Introducing the AWSSQLServer-DBCC Automation document for SQL Server maintenance
- Introducing Maintenance Windows for scheduling Automation documents against your SQL Server instances
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.