For an instructor lead, in-depth look at learning SQL click below.
As database administrators, one of our main responsibilities is ensuring the optimal performance of our databases. Therefore, a major part of our work involves monitoring database activity to identify and resolve potential issues. An accessible yet comprehensive tool at our disposal for this activity is SQL Server Extended Events (XEvents), a lightweight performance monitoring system that helps in capturing and analyzing SQL Server workloads.
What are SQL Server Extended Events?
First introduced in SQL Server 2008, Extended Events (XEvents) provides a way to diagnose performance problems within SQL Server. It offers a highly scalable and configurable data collection method and allows for correlation of SQL Server events with OS and application events, making it easier to understand the sequence of occurrences leading to a specific issue.
Setting Up an Extended Event Session
To set up an Extended Event session, we will use a few T-SQL commands. In the following example, we are creating a session called ‘mySession’, that will start automatically when the server starts. It will capture the ‘sql_batch_completed’ event, which gets executed after every batch of SQL has been run and the results returned to the client.
|
1 2 3 4 5 6 7 8 9 |
CREATE EVENT SESSION [mySession] ON SERVER ADD EVENT sqlserver.sql_batch_completed (SET collect_batch_text=(1)) 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=ON) GO |
In the code above, I have also added a setting to collect the batch text with each event collected. The various other settings configure memory management, event dispatch latency, etc. After creating the session, you can start it by using the following command.
|
1 2 3 4 |
ALTER EVENT SESSION [mySession] ON SERVER STATE = start; GO |
Querying Data from an Extended Event Session
Once the session has started, it begins to fill its target with data. You can query this data directly from SQL Server Management Studio (SSMS) using the built-in functions, as shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT object_name, file_name, name, target_data FROM sys.dm_xe_session_targets AS st JOIN sys.dm_xe_sessions AS s ON s.address = st.event_session_address WHERE s.name = 'mySession'; GO |
This will return an XML data set that contains all the events captured since starting the session. You can parse through this XML using built-in SQL Server XML functions.
Conclusion
The SQL Server Extended Events is a powerful tool allowing the monitoring and troubleshooting of database events directly from SSMS. With their scalabiltity, Extended Events prove you with concise and detailed snapshots into your SQL server’s behavior, allowing you to diagnose and resolve possible problems quicker.
Please note, while extended events offer immense power and flexibility, they should be implemented with care. If misconfigured, it can result in unintended resource consumption, so it’s essential to understand what every configuration does before creating an event session. Happy querying!