Monitor by using Extended Events

Extended Events is a powerful feature in Microsoft Azure SQL Solutions that allows administrators to monitor various activities and events occurring within the database. In this article, we will explore how to use Extended Events to effectively monitor your Azure SQL Solutions and gain valuable insights into the performance and health of your databases.

Creating an Extended Events Session

To begin monitoring your Azure SQL Solution using Extended Events, you first need to create an Extended Events session. An Extended Events session serves as a container for collecting and storing events. You can create a session using the following T-SQL script:

sql
CREATE EVENT SESSION MyXESession ON DATABASE
ADD EVENT sqlserver.database_file_size_change(
ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text)
WHERE ([sqlserver].[database_id]=(5) AND [severity]>=(0)))
ADD TARGET package0.event_file (SET filename=’C:\XE\MSSQL\MyXESession.xel’,max_file_size=(5),max_rollover_files=(10))
WITH (STARTUP_STATE=ON);

In the above example, we create an Extended Events session named “MyXESession” on the database. We specify the event we want to capture using the ADD EVENT statement. In this case, we capture the “sqlserver.database_file_size_change” event. We also define various actions associated with the event, such as capturing client hostname, database ID, username, and more.

Next, we specify the target where the captured events will be stored. In this example, we use the event_file target and specify the file path and size limits for the captured events.

Starting and Stopping the Extended Events Session

Once the session is created, you can start or stop the session using the following commands:

sql
ALTER EVENT SESSION MyXESession ON DATABASE STATE = START;
ALTER EVENT SESSION MyXESession ON DATABASE STATE = STOP;

Querying Captured Events

Now that the Extended Events session is up and running, you can query the captured events to gain insights into the database activities. You can use the sys.fn_xe_file_target_read_file function to read the captured events from the event file. For example:

sql
SELECT *
FROM sys.fn_xe_file_target_read_file(‘C:\XE\MSSQL\MyXESession*.xel’, NULL, NULL, NULL);

This query will retrieve all the captured events from the event file. You can further filter and analyze the data to identify performance bottlenecks, long-running queries, or any other issues impacting the database performance.

Exploring System-Defined Events

In addition to capturing specific events, Extended Events also provides a wide range of system-defined events that can be used for comprehensive monitoring. You can explore the available system-defined events in the SQL Server documentation for further customization.

It’s important to note that Extended Events impose minimal overhead on the database system, making them an efficient choice for continuous monitoring. However, you should carefully select the events to capture to avoid excessive resource usage.

Conclusion

Extended Events offer a powerful and flexible mechanism for monitoring Azure SQL Solutions. By creating an Extended Events session, capturing relevant events, and analyzing the collected data, you can gain valuable insights into the performance and health of your databases. Utilize the capabilities of Extended Events to effectively diagnose and troubleshoot any issues, ensuring optimal performance for your Azure SQL Solutions.