Monitor deadlocks in Amazon RDS for SQL Server and set notifications using Amazon CloudWatch

July 2023: This post was reviewed for accuracy.

Monitoring SQL Server is an essential aspect of any relational database management system (RDBMS) when dealing with performance problems. Many of our customers want to capture certain systems and user-defined events for monitoring and troubleshooting problems related to SQL Server. SQL Server logs these events in SQL Server error logs and SQL Server agent logs.

Deadlocks are one such event can be captured in SQL Server error logs. A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward. When this situation occurs, there is no way for these processes to resolve the conflict, so SQL Server automatically chooses one of the processes as the victim of the deadlock and rolls back the process, and the other process succeeds.

By default, when this occurs, your application may see or handle the error, but nothing is captured in the SQL Server error log or the Windows event log to let you know this occurred. The error message that SQL Server sends back to the client is similar to the following:

Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID xx) was deadlocked on {xxx} resources with another process 
and has been chosen as the deadlock victim. Rerun the transaction

With Amazon Relational Database Service (Amazon RDS) for SQL Server, you can now monitor the deadlocks and send Amazon Simple Notification Service (Amazon SNS) notifications as soon as a deadlock event occurs on an RDS for SQL Server instance. This can help you automate deadlock reporting and take appropriate actions to resolve deadlock conflicts.

This solution isn’t limited to capturing deadlock events; you can scale this solution to monitor other system and user-defined events captured in the error logs and SQL Server agent logs.

In this post, we show you how to publish error and agent log events directly to Amazon CloudWatch Logs and set up CloudWatch alarms and SNS notifications for the deadlock events that match the filter pattern that you create.

Solution overview

The following diagram illustrates the solution architecture.

To implement the solution, we walk through the following high-level steps:

1. Enable deadlock detection for Amazon RDS for SQL Server.
2. Publish the SQL Server error logs to CloudWatch.
3. Simulate a deadlock event.
4. Create a filter pattern and CloudWatch alarm.
5. Monitor the solution using Amazon RDS Performance Insights.

Prerequisites

• Amazon RDS for SQL Server
• Access to the AWS Management Console and Amazon CloudWatch
• An email address to receive notifications
• SQL Server Management Studio (SSMS)

Enable deadlock detection for Amazon RDS for SQL Server

To enable deadlock detection, complete the following steps.

1. On the Amazon RDS console, choose Parameter groups in the navigation pane.
2. Choose Create parameter group.
3. For Parameter group family, choose the SQL Server version and edition you are using.
   For example, SQL Server 2017 Standard Edition uses sqlserver-se-14.0.
4. Enter a Group name and Description.
5. Choose Create.
6. On the Parameter groups page, choose the group that you created in the previous step.
7. Choose Edit parameters, and select 1204 and 1222.
8. Edit the Values for 1204 and 1222 to 1.
9. Choose Preview changes.
10. On the next page, choose Save changes.
    
11. In the navigation pane, choose Databases.
12. In the DB identifier section, choose your RDS DB instance.
13. Choose Modify.
14. From the Database options section, for DB parameter group, choose the parameter group you created.

DB instances require a manual reboot in the following circumstances:

• If you replace the current parameter group with a different parameter group
• If you modify and save a static parameter in a custom parameter group

Publish the SQL Server error logs to CloudWatch

To publish your SQL Server error logs to CloudWatch, complete the following steps.

1. On the Modify page of the Amazon RDS console, in the Error logs section, choose Error log.
   This makes sure that the SQL Server error logs are published to CloudWatch Logs.
2. Choose Continue.
3. Enable Performance Insights for Amazon RDS.
4. In the Scheduling of modifications section, choose Apply immediately, and then choose Modify DB Instance.
5. On the Databases page, choose your RDS DB instance, then choose Actions.
6. Reboot the RDS DB instance for the changes to take effect.

Simulate a deadlock event

Simulate a deadlock transaction on your RDS for SQL Server instance by running the following T-SQL code in SQL Server Management Studio (SSMS).

--Two global temp tables with sample data for demo purposes.
CREATE TABLE ##Employees (
    EmpId INT IDENTITY,
    EmpName VARCHAR(16),
    Phone VARCHAR(16)
)
GO
 
INSERT INTO ##Employees (EmpName, Phone)
VALUES ('Amy', '900-999-1332'), ('Jay', '742-234-2222')
GO
 
CREATE TABLE ##Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Location VARCHAR(16)
)
GO
 
INSERT INTO ##Suppliers (SupplierName, Location)
VALUES ('ABC', 'New York'), ('Honest Sourcing', 'Boston')
GO

Next, open two query windows in SSMS. Run the following code in each of the sessions, step by step, in two windows.

After you run the code, you can see one of the transactions is processed (see the following screenshot).

The following screenshot shows the second transaction is blocked with a deadlock error.

Create a filter pattern and CloudWatch alarm

1. On the CloudWatch console, under Logs, choose Log groups.
2. Choose the SQL Server error logs of your RDS DB instance.
   The logs are listed in the following format:(/aws/rds/instance/<Your-RDS-Instance-Name>/error)
3. Choose Create metric filter.
4. In the Filter Pattern section, enter deadlock.
5. Select any errors to monitor and use that as the filter word.
6. Choose Assign metric.
7. Enter deadlock in both the Filter Name and Metric Name
8. Set the metric value field to 1.
9. Choose Create Filter.
   The following screenshot shows your filter details.
   
10. After the deadlock filter is created, choose Create alarm.
11. On the Specify metric and conditions page, for Metric name, enter deadlock.
12. For Statistic, choose Minimum.
13. For Period, choose the time period for the alarm, for example, 1 minute.
14. In the Conditions section, for Threshold type, choose Static.
15. For Whenever Deadlock is, choose Greater > threshold.
16. For Than, enter 0.
17. Choose Next.
18. In the Notification section, for Alarm state trigger, choose In alarm.
19. Select an SNS topic, or choose Create new topic to create an SNS topic using the email address you want to receive alerts.
20. Choose Next.
21. In the Name and description section, enter a name and description for your alarm.
22. Choose Next.
23. On the Preview and create page, review your alarm configuration, then choose Create alarm.
24. Confirm the notification email.

After following these steps, simulate a deadlock again. Once the alarm has enough data, it switches to in OK status if no error is detected, otherwise it switches to in ALARM status. When switching to in ALARM status, the CloudWatch alarm sends an SNS notification to the email that you specified (see the following screenshot)

Monitor using Performance Insights

Performance Insights is an Amazon RDS feature that can automatically analyze the current workload of a database instance and identify the queries that are slowing it down. For each query, it can show the type of wait it’s causing and the user who is causing the slowness or the client machine that’s running the query. All this information is made available in a compact, easy-to-understand dashboard, which makes Performance Insights a great tool for troubleshooting.

To get this kind of information, Performance Insights queries the RDS instance’s internal data structures in memory one time every second. It’s not a disk-based operation, so the sampling doesn’t put any pressure on the system. Later, we talk about the types of data that are collected during the samplings.

To use Performance Insights for Amazon RDS for SQL Server, complete the following steps.

1. Modify the RDS for SQL Server instance settings to enable Performance Insights.
   
2. On the Amazon RDS console, choose the database you want to monitor.
3. On the Monitoring tab, choose the Monitoring menu and choose Performance Insights.
   
4. You can choose the deadlock metrics and monitor using Performance Insights as well.
   

Clean up

When you’re finished using the resources in this post, clean up the AWS resources to avoid incurring unwanted charges. Specifically, delete the RDS for SQL Server instance and CloudWatch logs.

Conclusion

In this post, we showed how to publish error and agent log events directly to CloudWatch Logs and then set up a CloudWatch alarm and SNS notification for deadlock events that match a specific filter pattern. With this solution, you can automate RDS for SQL Server error log files monitoring and alerting. This can help you automate deadlock reporting and take appropriate actions to resolve deadlocks.

You can use this solution for monitoring other RDS for SQL Server log events and fatal errors. We showed an example on how to capture deadlock event metrics using Performance Insights.

To learn more about monitoring Amazon RDS for SQL Server, see Monitoring OS metrics using Enhanced Monitoring and Monitoring with Performance Insights on Amazon RDS.

About the authors

Yogi Barot is Microsoft Specialist Senior Solution Architect at AWS, she has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth AWS knowledge and expertise in running Microsoft workload on AWS.

Ganapathi Varma Chekuri is a Database Specialist Solutions Architect at AWS. Ganapathi works with AWS customers providing technical assistance and designing customer solutions on database projects, helping them move their existing databases to AWS cloud.