SQL Server Extended Events for Performance Monitoring - A Quick Start

SQL Server Extended Events provide a powerful mechanism for monitoring and troubleshooting database performance. In this quick start guide, we'll cover the basics of using Extended Events, their importance for performance monitoring, and provide sample code snippets to help you get started with capturing and analyzing performance data in SQL Server.

Why Use Extended Events?

Extended Events offer several advantages for performance monitoring:

• Granular Monitoring: You can capture detailed information about queries, system events, and more.
• Low Overhead: Extended Events have minimal impact on server performance when properly configured.
• Customization: You can tailor event sessions to capture specific data relevant to your monitoring needs.

Creating an Extended Events Session

Let's create a simple Extended Events session to capture query-related events:

-- Create an Extended Events session
CREATE EVENT SESSION MyPerfSession
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
ADD TARGET package0.asynchronous_file_target
    (SET FILENAME = N'C:PerfLogsMyPerfSession.xel', METADATAFILE = N'C:PerfLogsMyPerfSession.xem');
GO
-- Start the session
ALTER EVENT SESSION MyPerfSession ON SERVER
STATE = START;
    

Viewing Captured Events

To view captured events, you can query the target file using the Extended Events functions:

-- Query captured events
SELECT
    event_data.value('(event/@name)[1]', 'nvarchar(100)') AS EventName,
    event_data.value('(event/action[@name=`duration`]/value)[1]', 'bigint') AS Duration
FROM
    sys.fn_xe_file_target_read_file(
        'C:PerfLogsMyPerfSession*.xel', null, null, null
    ) AS file
CROSS APPLY sys.fn_xe_file_target_read_file(
    file, null, null, null
) AS event_data;
    

What's Next?

As you become more proficient with SQL Server Extended Events, explore advanced topics such as customizing event sessions, filtering events, and using the collected data to optimize your database's performance.