How do I manage and troubleshoot Amazon RDS for SQL Server instances?

Resolution

Monitor availability

Use Amazon CloudWatch Database Insights to monitor performance and health.

To view Database Insights metrics for an RDS for SQL Server instance, complete the following steps:

1. Open the CloudWatch console.
2. In the navigation pane, choose Infrastructure Monitoring, and then choose Database Insights.
3. Under Database Views, choose Database Instance.
4. Under Search by properties and tags, select your RDS for SQL Server instance.
5. On the Database Instance Dashboard, choose the Database Telemetry tab, and then choose the Metrics tab.

For more information, see Get started with CloudWatch Database Insights.

To get notified when the usage patterns change from your baseline or your deployment reaches its capacity quota, create CloudWatch alarms. Configure CloudWatch alarms to notify you when input/output operations per second (IOPS) are reaching your provisioned IOPS quota and throughput is reaching your provisioned throughput quota.

Monitor the following Amazon RDS instance-level metrics in CloudWatch:

• CPUUtilization for CPU usage
• FreeableMemory for memory usage
• FreeStorageSpace for available storage
• ReadIOPS and WriteIOPS for the average number of read and write I/O operations per second
• ReadThroughput and WriteThroughput for the average number of bytes read and written from disk per second
• ReadLatency and WriteLatency for the average amount of time taken per disk I/O operation
• DiskQueueDepth for the number of I/O operations that are waiting to access the disk

For instances with burst capacity, you can also monitor the following metrics:

• EBSIOBalance%
• EBSByteBalance%

Note: There are throughput quotas at both the instance level and the volume level. For more information, see How do I troubleshoot the latency of Amazon EBS volumes caused by an IOPS bottleneck in my Amazon RDS instance?

For low values that might show memory pressure, monitor the metrics for the Buffer cache ratio and Page life expectancy SQL Server Buffer Manager counters.

For high values that might show memory pressure, monitor the metric for the Memory Grants Pending SQL Server Memory Manager counter.

If the Processes blocked SQL Server General Statistics counter remains at a high value for an extended period, then there might be transactions that are holding locks.

Establish a baseline level for each metric, and then monitor whether the values change from the baseline.

Use a larger instance class size to improve performance

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

To scale your database resources to meet performance needs, increase your RDS for SQL Server instance class size. Before you increase your instance class, see Considerations for SQL Server upgrades.

To verify that the instance type that you want is available for your SQL Server version and AWS Region, run the following describe-orderable-db-instance-options AWS CLI command:

aws rds describe-orderable-db-instance-options \  
    --engine your-engine \  
    --engine-version your-version \  
    --query 'OrderableDBInstanceOptions[].{InstanceClass:DBInstanceClass,StorageType:StorageType, AvailabilityZones:AvailabilityZones}' \  
    --region your-region

Note: Replace your-engine with your SQL Server engine, your-version with your SQL Server engine version, and your-region with your Region.

If your preferred instance type isn't available for your SQL Server version in your AWS Region or Availability Zone, then take one of the following actions:

• Use a supported instance type that has similar specifications. For example, if db.r6i.2xlarge isn't available, then you might use db.r5.2xlarge that has the same number of vCPUs and RAM. To compare costs, use the AWS Pricing Calculator.
• If you require an instance type that isn't available, then you might need to upgrade your SQL Server version. Or, migrate to a different Region or Availability Zone that supports the instance type.

Resolve I/O throttling

RDS for SQL Server instances might experience IOPS and throughput throttling that can cause query delays and block the database.

To determine whether your instance is experiencing I/O throttling, monitor the following metrics:

• ReadLatency and WriteLatency are showing unusual spikes.
• DiskQueueDepth is consistently high. A high value shows that a large number of I/O requests are queueing.
• IOPS or throughput reached provisioned quotas.
• EBSIOBalance% or EBSByteBalance% reached 0% on instances with burst capacity.

To resolve throttling, take the following actions:

• Increase provisioned IOPS or throughput.
  Note: When you increase provisioned IOPS and throughput, you incur additional costs. To estimate your costs, use the AWS Pricing Calculator.
• Increase the instance class. A larger instance class provides higher instance-level I/O quotas.
• Reduce unnecessary I/O with appropriate indexes and efficient query plans. Avoid full table scans.
• Change your storage type. If you're using Amazon Elastic Block Store (Amazon EBS) General Purpose SSD volumes, the switch to Provisioned IOPS SSD volumes for workloads that require consistent I/O performance.

Resolve a blocked database

If transaction locks continue for extended periods, then your database can become blocked and experience performance issues.

To identify, resolve, and prevent continual transaction locks in your RDS for SQL Server instance, complete the following steps:

1. Use SQL Server Management Studio (SSMS) or another SQL client to connect to your RDS for SQL Server instance.
2. Run the following query to identify the head of a multiple-session blocking chain and the query text of the sessions in the blocking chain:

   WITH cteBL (session_id, blocking_these) AS   
   (SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s   
   CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '    
                   FROM sys.dm_exec_requests as er  
                   WHERE er.blocking_session_id = isnull(s.session_id ,0)  
                   AND er.blocking_session_id <> 0  
                   FOR XML PATH('') ) AS x (blocking_these)  
   )  
   SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these  
   , batch_text = t.text, input_buffer = ib.event_info, *   
   FROM sys.dm_exec_sessions s   
   LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id  
   INNER JOIN cteBL as bl on s.session_id = bl.session_id  
   OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t  
   OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib  
   WHERE blocking_these is not null or r.blocking_session_id > 0  
   ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

For more information, see Understand and resolve SQL Server blocking problems on the Microsoft Learn website.

If the blocking session is in the Rollback state, then you can't forcefully stop it. To maintain data consistency, you must wait until SQL Server completes rollback operations. The duration of the rollback depends on how long the transaction was running and the amount of work it performed.

If the head blocker doesn't resolve and you must stop the session, then run the following command:

KILL [target SPID];

To prevent continual transaction locks, take the following actions:

• Revise queries to reduce long-running transactions. For example, you can add appropriate indexes, improve query plans, and create smaller transactions where possible.
• Use CloudWatch Database Insights to monitor database performance.
• Determine whether the instance-level and volume-level IOPS and throughput are sufficient. For more information, see How do I troubleshoot the latency of Amazon EBS volumes caused by an IOPS bottleneck in my Amazon RDS instance?
  Note: When you increase provisioned IOPS and throughput, you incur additional costs. To estimate your costs, use the AWS Pricing Calculator.
• Implement Amazon RDS best practices for SQL Server.
• Create CloudWatch alarms to monitor database metrics, such as CPU utilization, memory usage, and storage space.

Related information

How do I troubleshoot high CPU utilization on my Amazon RDS for SQL Server instance?

DB instance classes

Modifying an Amazon RDS DB instance