How do you use Extended Events in SQL Server for monitoring and performance analysis? — SQL Server Interview Question #49

Extended Events in SQL Server is a powerful tool used for the collection of diagnostic and performance data of SQL Server. It enables database administrators to monitor and capture events in real-time with lightweight performance overhead.

In SQL Server, several event classes are already defined, and administrators can use these event classes to observe the behavior of the system. However, using Extended Events, administrators can create custom event sessions that are tailored to specific monitoring requirements.

Here are the steps to create an Extended Event session in SQL Server:

1. Launch the SQL Server Management Studio (SSMS), connect to the SQL Server instance, and expand the Management node.

2. Right-click the Extended Events folder and select the New Session Wizard to launch the Create New Session Wizard.

3. Enter the name of the new event session, select the event source, and configure the event details.

4. Define the events and data you want to capture by adding event modules to the event session.

5. Specify the actions to be taken when the events occur, such as writing the event data to a file or sending an email notification.

6. Set the session event filter and other advanced configurations.

7. Start the session, and monitor the events as they occur.

One of the significant advantages of using Extended Events is that it consumes minimal system resources and has a low overhead. A well-designed Extended Event session can provide meaningful insights into the performance of a SQL Server instance with negligible performance impact.

For example, suppose we wish to monitor the T-SQL Statements executed on a specific database. In that case, we can create a new session, configure the event details, and add the SQL statement event module to the session. Here’s what the script would look like:

CREATE EVENT SESSION [TSQL Tracing] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text,sqlserver.tsql_stack)
    WHERE ([database_name]='MyDatabase'))
ADD TARGET package0.asynchronous_file_target(
    SET filename='C:ExtendedEventLogsTSQLTracing.xel',
    metadatafile='C:ExtendedEventLogsTSQLTracingMetadata.xem'
    )
WITH (MAX_DISPATCH_LATENCY=5 SECONDS)
GO

ALTER EVENT SESSION [TSQL Tracing] ON SERVER STATE=START;

In this example, we have created a new event session to capture completed SQL statements executed on the "MyDatabase" database. The event data is written to an asynchronous file target located at "C:/ExtendedEventLogs/". We have also set the maximum dispatch latency to 5 seconds, which defines the maximum time it may take for events to be dispatched to the target.

In conclusion, Extended Events are a powerful tool for monitoring and performance analysis in SQL Server. By designing well-crafted event sessions, we can capture relevant events and data with negligible performance overhead, leading to improved system performance and user experience.