How do I troubleshoot and monitor performance issues with Amazon RDS for SQL Server instances?

Resolution

Identify the cause of the performance issues

To identify what's causing the performance issues, take the following actions.

Check the RDS DB instance class

Take the following actions:

• Compare current resource utilization against the specifications of your DB instance class to determine whether you exceeded capacity.
• Identify whether CPU, memory, or network constraints affect the performance of your DB instance.
• Check whether your DB instance's baseline and burst input/output operations per second (IOPS) meet your workload requirements and don't exceed the instance quotas.
• Determine whether memory-optimized or compute-optimized DB instances improve performance for your workload.

Analyze IOPS metrics with Amazon CloudWatch

Take the following actions:

• Monitor your DB instance's ReadIOPS and WriteIOPS metrics against your provisioned IOPS quotas.
• Check the ReadIOPs and WriteIOPs metrics for your Amazon Elastic Block Store (Amazon EBS) volume to identify storage bottlenecks.
• If you use gp2 volumes, then monitor the BurstBalance metric to confirm that burst credits remain available.
• If you use io1 or io2 volumes, then confirm that your provisioned IOPS capacity matches your workload's IOPS usage.

For more information, see Amazon CloudWatch metrics for Amazon RDS and Monitoring Amazon RDS metrics with Amazon CloudWatch.

Check the DB load

Use CloudWatch Database Insights to check the DB load when you're experiencing issues, and identify top SQL queries and wait events that contribute to high load.

Note: The standard mode of Database Insights is turned on by default for your database.

Review SQL Server extended events

Complete the following steps:

1. Install SQL Server Management Studio (SSMS) to connect to your RDS for SQL Server DB instance. For instructions, see Install SQL Server Management Studio on the Microsoft website.

2. Navigate to your extended event sessions. For instructions, see Create an event session in SSMS on the Microsoft website.

3. View live data in the extended event sessions. For instructions, see Watch live data on the Microsoft website.

4. Look for xml_deadlock_report events in the live data stream.

5. Run the following T-SQL query to retrieve deadlock information:

   SELECT 
       xed.value('@timestamp', 'datetime') AS [Timestamp],
       xed.query('.') AS [Deadlock XML]
   FROM 
   (
       SELECT CAST(target_data AS XML) AS target_data
       FROM sys.dm_xe_session_targets AS xt
       INNER JOIN sys.dm_xe_sessions AS xs
       ON xs.address = xt.event_session_address
       WHERE xs.name = N'system_health'
       AND xt.target_name = N'ring_buffer'
   ) AS XML_Data
   CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
   ORDER BY [Timestamp] DESC;

   Note: The preceding query retrieves only the currently recorded events in the ring buffer. For historical data, use the sys.fn_xe_file_target_read_file function to read the .xel files.

Resolve performance issues

To resolve performance issues, take the following actions.

Check for high CPU utilization

If your database has high CPU utilization, then take the following actions:

• Use the Top 10 instances per DB Load Utilization chart in Database Insights to identify queries with high CPU utilization.
• Analyze your execution plans for slow queries.
• Increase the maximum degree of parallelism (MAXDOP) in the DB parameter group for resource-intensive queries.

For more information, see How do I troubleshoot high CPU utilization on my Amazon RDS for SQL Server instance?

Identify and resolve deadlocks or blocked sessions

If you experience deadlocks or blocked sessions, then take the following actions.

Run the following query to identify blocked sessions and deadlocks:

 — Information about the blocked session
select
 DB_NAME(r.database_id) AS DatabaseName,
 r.session_id AS BlockedSPID,
 s.login_name AS BlockedLogin,
 s.host_name AS BlockedHost,
 r.command,
 wt.wait_type,
 wt.wait_duration_ms,
 r.wait_resource,
 BlockedSQL.text AS BlockedQueryText,
 r.blocking_session_id AS BlockingSPID,
 bs.login_name AS BlockingLogin,
 bs.host_name AS BlockingHost
FROM
 sys.dm_exec_requests AS r
INNER JOIN
 sys.dm_exec_sessions AS s
 ON r.session_id = s.session_id
INNER JOIN
 sys.dm_os_waiting_tasks AS wt
 ON r.session_id = wt.session_id
INNER JOIN
 sys.dm_exec_sessions AS bs
 ON r.blocking_session_id = bs.session_id
LEFT JOIN
 sys.dm_exec_requests AS br
 ON r.blocking_session_id = br.session_id
CROSS APPLY
 sys.dm_exec_sql_text(r.sql_handle) AS BlockedSQL
OUTER APPLY
 sys.dm_exec_sql_text(br.sql_handle) AS BlockingSQL
WHERE
 r.blocking_session_id <> 0;

After you identify blocked sessions, analyze deadlock graphs from the system_health extended event session. Then, identify the resources and queries that cause the deadlock. To retrieve deadlock information from the system_health session, see How can I capture information about a deadlock on my Amazon RDS DB instance that runs on SQL Server?

To reduce deadlocks, take the following actions:

• Optimize the transaction isolation levels.
• Verify that the access order for resources across transactions is consistent.
• Minimize the transaction duration and resource hold time.
• Use appropriate indexing to reduce lock contention.

Resolve slow disk operations

If you experience slow disk read or write operations, then take the following actions:

• Monitor disk I/O metrics in CloudWatch.
• Optimize your queries to reduce unnecessary I/O operations.
• Use read replicas to offload read traffic from the primary DB instance.

Resolve resource contention between workloads

If you experience resource contention between workloads in RDS for SQL Server Enterprise Edition, then activate resource governor.

Note: The resource governor feature is available only in SQL Server Enterprise Edition.

After you activate resource governor, complete the following steps:

1. Create or modify an option group, and then add the resource governor to the option group.
2. Configure your workload groups and resource pools to set CPU and memory quotas for different application requirements.
3. Associate the option group with your DB instance for granular resource control between critical and non-critical workloads.

For more information, see Optimize database performance using resource governor on Amazon RDS for SQL Server.

Implement best practices to manage your RDS for SQL Server workload

Take the following actions:

• Use Database Insights and CloudWatch to periodically review metrics, such as CPUUtilization, DatabaseConnections, and FreeStorageSpace.
• Create a CloudWatch alarm to identify spikes in CPU utilization.
• Increase allocated storage before you reach capacity quotas.
• Create custom widgets in Database Insights to monitor specific metrics, such as for deadlocks.
  Note: When you create a new deadlock monitoring widget, search for deadlock. Then, choose the Number of deadlocks total metric.
• Review and modify your DB instance type to meet your performance requirements.
• Identify trends and address potential issues before they become critical.
• Analyze DB load to identify SQL query issues that affect performance.
• Analyze wait events in Database Insights to identify performance bottlenecks in your DB operations.

Related information

Monitoring metrics in an Amazon RDS instance

Deadlocks guide on the Microsoft website.

Using extended events with Amazon RDS for Microsoft SQL Server

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