Introduction to SQL Server Extended Events for Performance Monitoring

SQL Server's Extended Events framework is a powerful and flexible system designed to help users gather as much or as little diagnostic data as needed to troubleshoot or analyze performance issues. Its architecture is highly configurable and scales efficiently, making it an essential tool for database administrators.

Benefits of Using Extended Events

Extended Events is a lightweight monitoring mechanism that minimizes the impact on server performance while capturing detailed diagnostic information. SQL Server offers two graphical interfaces that enable users to create, modify, view, and analyze event sessions with ease.

Exploring Extended Events System Views with T-SQL

SQL Server provides several system catalog views to inspect Extended Events sessions and their components. Here are some useful queries to explore event session definitions, fields, actions, targets, and events:

-- List all event session definitions in SQL Server
SELECT * FROM sys.server_event_sessions;

-- List each customizable column explicitly set on events and targets
SELECT * FROM sys.server_event_session_fields;

-- List each action associated with events in a session
SELECT * FROM sys.server_event_session_actions;

-- List each event target for event sessions
SELECT * FROM sys.server_event_session_targets;

-- List all events configured in an event session
SELECT * FROM sys.server_event_session_events;

Creating an Extended Event Session to Capture Errors

The following example demonstrates how to create an Extended Event session that captures all errors with severity greater than 10, along with useful context information such as client application name, hostname, and SQL text:

CREATE EVENT SESSION [CatchError] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname,
           sqlserver.database_id, sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.username)
    WHERE ([severity] > 10))
ADD TARGET package0.event_file(
    SET filename = N'CatchError.xel', 
        max_file_size = 5, 
        max_rollover_files = 5, 
        metadatafile = N'CatchError.xem')
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

To start this session, run:

ALTER EVENT SESSION CatchError ON SERVER STATE = START;
GO

Querying the Captured Event Data

Once your Extended Event session is running and capturing data, you can query the event files using the following method. This example reads and formats the error information stored in the event files:

;WITH events_cte AS (
    SELECT 
        DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), xevent.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS err_timestamp,
        xevent.event_data.value('(event/data[@name="severity"]/value)[1]', 'bigint') AS err_severity,
        xevent.event_data.value('(event/data[@name="error_number"]/value)[1]', 'bigint') AS err_number,
        xevent.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(512)') AS client_hostname,
        xevent.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(512)') AS client_app_name,
        xevent.event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(512)') AS err_message,
        xevent.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
        xevent.event_data
    FROM sys.fn_xe_file_target_read_file('CatchError*.xel', 'CatchError*.xem', NULL, NULL) AS xevents
    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) AS xevent
)
SELECT * 
FROM events_cte
ORDER BY err_timestamp;

Visualizing Extended Events

The Extended Events sessions and their data can also be viewed and managed graphically within SQL Server Management Studio (SSMS). Below is an example screenshot demonstrating the interface:

Additional Resources

For more detailed information, please refer to the official Microsoft documentation and catalog views:

Using Extended Events allows SQL Server administrators to perform detailed diagnostics while maintaining minimal performance overhead, making it an indispensable tool for effective database management.

SQL Server help

Monday, April 6, 2020

How to use extended events to catch all error in SQL Server