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.
