For an instructor lead, in-depth look at learning SQL click below.
When working with SQL Server, performance monitoring and tuning is a crucial aspect that needs constant attention. For this purpose, SQL Server introduces a feature known as ‘Extended Events’ or ‘XEvents’. The Extended Events is a lightweight performance monitoring system that allows DBAs and developers to collect data to monitor and troubleshoot performance issues.
What are SQL Server Extended Events?
Extended Events is a performance monitoring system that helps in collecting and analyzing SQL Server’s health data. It’s a robust infrastructure that provides large scale of information about server’s performance problems.
Let’s look at how to create a Session to understand this feature more precisely. A Session could be thought of as a process in which you define what specific data needs to be collected for performance monitoring. After creating a session, it will start to collect specified events data.
Creating a Session
|
1 2 3 4 5 6 7 8 |
CREATE EVENT SESSION [MonitorSession] ON SERVER ADD EVENT sqlserver.sql_batch_starting, ADD EVENT sqlserver.sql_batch_completed, ADD EVENT sqlserver.sql_statement_starting, ADD EVENT sqlserver.sql_statement_completed WITH (MAX_DISPATCH_LATENCY=5SECONDS) |
This script creates a Session named ‘MonitorSession’, and collects data about different SQL events such as batch starting, batch completion, statement starting, and statement completion. The ‘WITH’ clause determines how frequently the event data buffers would be dispatched.
Starting and Stopping a Session
Once we have a Session created, we can start the session to begin collecting data. Equally, we can stop a Session when we are done. Using the following commands, we can start and stop a Session accordingly.
|
1 2 3 4 5 6 7 |
-- To start a Session ALTER EVENT SESSION [MonitorSession] ON SERVER STATE=START; -- To stop a Session ALTER EVENT SESSION [MonitorSession] ON SERVER STATE=STOP; |
Viewing Session Data
In SQL Server Management Studio (SSMS), you can see the data collected by the Session through the ‘Extended Events’ section, usually located under the ‘Management’ folder.
Wrapping Up
SQL Server Extended Events are a powerful tool in the hands of DBAs and developers for performance monitoring and tuning. With custom Sessions, you can obtain exactly the types of data that are relevant to your use case. As always, practice with different types of events and sessions will give you the familiarity and expertise to fully utilize this feature.
Happy Querying!