Set up Extended Events in Amazon RDS for SQL Server
Amazon Relational Database Service (Amazon RDS) for SQL Server now supports Extended Events. Extended Events (XEvents) is a lightweight performance monitoring system that enables you to collect data to monitor and troubleshoot problems in SQL Server. Extended Events is automatically enabled for users with primary user privileges in Amazon RDS for SQL Server.
This post demonstrates how to use Extended Events on Amazon RDS for SQL Server, discusses considerations when working with Extended Events in a Multi-AZ environment, and provides examples of supported events and actions.
Before getting started, complete the following prerequisite steps:
- Download SQL Server Management Studio (SSMS).
- Log in to Microsoft SQL Server 2012 or higher.
- Ensure that your account has the server permission of ALTER ANY EVENT SESSION.
The text and supporting screenshots in this post can vary as the SSMS UI changes over time. However, the screenshots remain effective for explanation if the discrepancies are only minor.
Use Extended Events on Amazon RDS for SQL Server
Amazon RDS for SQL Server supports using XEvents on Standard and Enterprise Editions, starting from 2012 versions. You can use disk-based targets and memory-based targets for your XEvents sessions. Some recommendations and limitations apply when you work with XEvents on Amazon RDS for SQL Server; for more information, see Using extended events with Amazon RDS for Microsoft SQL Server. In this section, we go through an example of monitoring database create and database drop events using an XEvents session.
Create an XEvents session
To create an Extended Events session, complete the following steps:
- Connect to your RDS for SQL Server instance with SSMS.
- In the Object Explorer, under Management, choose Extended Events and then choose New Session Wizard.
- Enter a name for the session.
- Select Do not use a template. And click on Next to open New Session Wizard screen.
- For this post, I want to track database created and database dropped events.
- Choose any values in Global Fields, which are generally common to all events. For this post, I choose
username global fields.
- You can set Event Filters to limit the data you want to capture. In this post, we don’t select any filter options.
- Specify the data storage. You can save the data to a file and create historical records for later analysis. In addition, at the same time you can work with most recent data stored in ring buffer.Ring buffer target memory can’t exceed 4 MB in Amazon RDS for SQL Server. In addition, data to a file can only be saved on
- Choose Finish.You have successfully created your RDSXETEST XE session.You can configure certain XEvents session properties and XEvents target properties using
rdsadmin.dbo.rds_set_configuration. For more information, see Using extended events with Amazon RDS for Microsoft SQL Server.
- Before exiting SQL Management Studio window, select Start the event session and Watch Live data.
- You can see your
RDSXETESTsession in the XEvents Sessions menu in SSMS.
Monitor the events with the XEvents session
Now to test out Extended Events, let’s create and drop a test database from query editor:
When you watch the events live, you can see a create database event and drop database event captured.
You can also query the file target to see the same information:
XEvents target files are cleaned up based on the retention setting you have chosen. For more information, see Using extended events with Amazon RDS for Microsoft SQL Server.
Modify the XEvents session
To modify your XEvents session, complete the following steps:
- In the SSMS Object Explorer, choose your event (right-click).
- Choose Properties.
The same multi-page dialog is displayed as when you created the XEvents session.You can use the SSMS UI to generate a T-SQL script that created your event session.
- Choose your session node (right-click).
- Choose Script Session as.
- Choose CREATE to and Clipboard.
- Enter the code into your preferred text editor.
The create statement for your session is similar to the following:
Drop the XEvents session
You can either use SSMS to drop your XEvents session or a T-SQL command to drop it. The following T-SQL code drops the
Considerations for Multi-AZ deployments
When you create an Extended Event session on a primary DB instance, it doesn’t propagate to the standby replica. You can fail over and create the XEvents session on the new primary DB instance. Alternatively, you can remove and re-add the Multi-AZ configuration to propagate the session to the standby replica. Amazon RDS stops all non-default XEvents sessions on the standby replica so these sessions don’t consume resources on the standby. Because of this, after a standby replica becomes the primary DB instance, you must manually start the Extended Event sessions on the new primary.
This applies to both Always On availability groups and database mirroring.
If you want your XEvents sessions to always be running on a primary instance of your Multi-AZ, you can create an agent job using a sample T-SQL such as the following to start your XEvents session on the primary if the session isn’t already started (you may have to adjust the code for your instance version and edition):
The preceding code creates a SQL agent job that does the following:
- Runs with a 10-minute interval
- Checks if the current instance is the primary or not
- If the current instance is primary and if the
RDSXETESTsession isn’t in a started state, it starts the session
You can modify these parameters as needed.
Examples of supported events and actions
You can create an XEvents session to collect most events and corresponding actions, but a few actions are not supported. For a full list of events and actions not supported, see Using extended events with Amazon RDS for Microsoft SQL Server.
The following are examples of supported events:
- Database events, such as
- Run events, such as
- Always On events, such as
- Memory events, such as
You can see the full list of events by using the following SQL query:
The following are examples of supported event actions:
- Collect SQL text (
- Collect current database name (
- Collect username (
- Collect session ID (
You can see the full list of actions by using the following SQL query:
Extended Events is a new and exciting feature in Amazon RDS for SQL Server. We support Extended Events only on Amazon RDS for SQL Server Enterprise and Standard Edition. You can use this feature to diagnose various performance issues on your RDS for SQL Server instance. To know more about Extended Events in Amazon RDS for SQL Server look into the article here.
About the author
Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with the AWS Database Services Organization (DBSO), Customer Advisory Team (CAT). He has led large scale Database Migration & Modernization engagements for Enterprise Customers to move their on-premises database environment to Multi Cloud based database solutions.
Joon Lee is a Software Development Engineer at Amazon Web Services. He works with the AWS RDS team, focusing on commercial database engines and SQL Server. He enjoys working on technical challenges in Amazon RDS and is passionate about learning new technologies.
Chinni Bolapati is a Senior Database Engineer with Amazon Web Services. He works on the Amazon RDS team, focusing on commercial database engines, SQL Server and Oracle.