SQL Server Performance Tuning

Introduction to Extended Events

Extended Events (EE) is a powerful tool for real-time monitoring, diagnostics, and troubleshooting of SQL Server. It provides a detailed view into your database activity, allowing you to identify performance bottlenecks and potential issues before they impact your users.

Unlike traditional SQL Server Profiler, Extended Events are lightweight and don't significantly impact database performance. They are designed to collect granular data without adding significant overhead.

Key Features:

Real-time Monitoring
Diagnostic Data Collection
Lightweight Performance
Customizable Event Sessions

Creating an Event Session

The first step in using Extended Events is to create an event session. You can configure the session to capture specific events based on your monitoring needs.

Example: Capturing Login Events


CREATE EVENT SESSION LoginEvents ON SERVER
ADD EVENT Login (
    SQLHOST_APP = ANY_VALUE,
    DATABASE_ID = ANY_VALUE
)
    WITH (MAX_DATA_SIZE = 2048KB, TRACK_NOWAIT);

Explanation:

`CREATE EVENT SESSION LoginEvents ON SERVER` - Creates a new event session named "LoginEvents" on the current server.
`ADD EVENT Login (...)` - Defines the events to be captured within the session.
`SQLHOST_APP = ANY_VALUE` - Captures information about the SQL Server application that initiated the login.
`DATABASE_ID = ANY_VALUE` - Captures information about the database being accessed.
`WITH (MAX_DATA_SIZE = 2048KB, TRACK_NOWAIT)` - Specifies the maximum amount of data that can be collected by the session and whether to track waits.

Querying Event Data

Once you've created an event session, you can query the collected event data using the `EVENTDATA` function.


SELECT *
FROM EVENTDATA()
WHERE start_time < GETDATE() - 1;

Explanation:

`SELECT * FROM EVENTDATA()` - Retrieves all data collected by the event session.
`WHERE start_time < GETDATE() - 1` - Filters the data to only include events that occurred within the last 24 hours.