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.

Prerequisites

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 database_id, client_hostname, and 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 D:\rdsdbdata\Log folder.
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 RDSXETEST session 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:

CREATE DATABASE XETEST
DROP DATABASE XETEST

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:

SELECT * FROM sys.fn_xe_file_target_read_file('d:\rdsdbdata\log\RDSXETEST*.xel', null, null, null)

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:

CREATE EVENT SESSION [RDSXETEST] ON SERVER 
ADD EVENT sqlserver.database_created(
    ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.username)),
ADD EVENT sqlserver.database_dropped(
    ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.username)) 
ADD TARGET package0.event_file(SET filename=N'D:\rdsdbdata\Log\RDSXETEST',max_file_size=(30)),
ADD TARGET package0.ring_buffer(SET max_memory=(2048))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

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 RDSXETEST session:

DROP EVENT SESSION RDSXETEST ON SERVER

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):

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'START_XE_SESSIONS',@enabled=1, @description=N'Job to start XE sessions.', 
		@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'STEP1', @step_id=1, @os_run_priority=0, subsystem=N'TSQL', 		@command=N'BEGIN
    IF (DATABASEPROPERTYEX(''rdsadmin'',''Updateability'')=''READ_WRITE''
    AND DATABASEPROPERTYEX(''rdsadmin'',''status'')=''ONLINE''
    AND (DATABASEPROPERTYEX(''rdsadmin'',''Collation'') IS NOT NULL OR DATABASEPROPERTYEX(''rdsadmin'',''IsAutoClose'')=1)
    )
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name=''RDSXETEST'')
            ALTER EVENT SESSION RDSXETEST ON SERVER STATE=START
    END
END', 
		@database_name=N'master'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'SCHD1', 
		@enabled=1, @freq_type=8, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=10, 
		@freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20210402, 
		@active_end_date=99991231, @active_start_time=0, @active_end_time=235959, 
		@schedule_uid=N'123fca70-cac7-406e-88ac-a4cd78041c61'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

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 RDSXETEST session 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 database_created
Run events, such as execution_warning
Always On events, such as alwayson_ddl_executed
Memory events, such as memory_manager_free_memory

You can see the full list of events by using the following SQL query:

SELECT * FROM sys.dm_xe_objects WHERE object_type='event';

The following are examples of supported event actions:

Collect SQL text (sql_text)
Collect current database name (database_name)
Collect username (username)
Collect session ID (session_id)

You can see the full list of actions by using the following SQL query:

SELECT * FROM sys.dm_xe_objects WHERE object_type='action'

Summary

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.

AWS Database Blog