DEV Community

Cover image for MSSQL EXTENDED EVENTS Stop Guessing and Start Targeting
Amar Abaz
Amar Abaz

Posted on

MSSQL EXTENDED EVENTS Stop Guessing and Start Targeting

#database #learning #sqlserver #tutorial

Instead of capturing everything and slowing down your system with SQL Profiler, XEvents allow you to execute a targeted, razor sharp seek.
It is lightweight, deeply integrated into the SQL Server engine, and gives you the exact data you need to debug without a performance penalty.

Once your session is running, you can stream the results live, inspect them temporarily in the memory ring buffer, save them directly to a file, or extract the XML data to load it straight into a readable table for advanced querying and reporting.
In this article, I won't talk about the GUI wizard. Instead, we will focus on my T-SQL scripts you can directly execute to start the seek for different use cases.

Earlier, I already shared how to use XEvents to track and report on DEADLOCK and BLOCKING sessions. 🤯
https://dev.to/abeamar/track-blocking-and-deadlocks-in-mssql-with-my-custom-script-1877
This time, we are diving deeper to understand what the underlying code really does, while exploring new scenarios you can use in your daily work.

🫡 Scenario 1: Catching Silent Errors

Applications often catch errors internally or suppress them, leaving you completely in the dark.
This script creates an XEvent catching every single error reported across the whole instance. It collects critical context like the exact SQL text, client hostnames, and query hashes, and dumps them into a file.

CREATE EVENT SESSION [Seek_ERRORS] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(
        package0.event_sequence, package0.last_error, sqlos.worker_address, 
        sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname, 
        sqlserver.client_pid, sqlserver.compile_plan_guid, sqlserver.context_info, 
        sqlserver.database_name, sqlserver.execution_plan_guid, sqlserver.nt_username, 
        sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, 
        sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_nt_username, 
        sqlserver.session_server_principal_name, sqlserver.sql_text, sqlserver.transaction_sequence, 
        sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username
    )
)
ADD TARGET package0.event_file(SET filename=N'C:\Temp\AbeSQLTrace.xel')
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
);
GO
-- Start the session immediately
ALTER EVENT SESSION [Seek_ERRORS] ON SERVER STATE = START;
-- Start automatically if the SQL Server restarts
ALTER EVENT SESSION [Seek_ERRORS] ON SERVER WITH (STARTUP_STATE=ON);
GO
Enter fullscreen mode Exit fullscreen mode

Once the session has been running and gathering data, you don't need a UI to read it. You can query the target file directly using T-SQL and cast the output to XML for easy viewing:

SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('C:\Temp\AbeSQLTrace*.xel', NULL, NULL, NULL);
Enter fullscreen mode Exit fullscreen mode

If you don't want to save the data to a physical file and would rather just inspect the events using the SSMS GUI in real-time, you can simply remove or comment out the file target line

--ADD TARGET package0.event_file(SET filename=N'C:\Temp\AbeSQLTrace.xel')
Enter fullscreen mode Exit fullscreen mode

When you do this, you can just right click the session in SSMS and select "Watch Live Data" to see the errors pop up on your screen on the fly.

If you only want to look at errors on a specific database or look for specific keywords mentioned in the SQL text, you can add a WHERE STATEMENT in you code for event, like this

    WHERE ([sqlserver].[sql_text] LIKE N'%AdventureWorks%') 
       OR ([sqlserver].[database_name] = N'AdventureWorks')
Enter fullscreen mode Exit fullscreen mode

🫡 Scenario 2: Tracking behavior on target objects

And now by using the WHERE statement with COMPLETED queries, you can actively track the behavior of specific object usage, and that targets a specific database. This allows you to track exactly who was accessing your tables, from which machines, and when.

CREATE EVENT SESSION [Monitor_OBJECTS] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(
        package0.event_sequence, package0.last_error, sqlos.worker_address, 
        sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname, 
        sqlserver.client_pid, sqlserver.compile_plan_guid, sqlserver.context_info, 
        sqlserver.database_name, sqlserver.execution_plan_guid, sqlserver.nt_username, 
        sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, 
        sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_nt_username, 
        sqlserver.session_server_principal_name, sqlserver.sql_text, sqlserver.transaction_sequence, 
        sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username
    )
    WHERE (
        ([sqlserver].[sql_text] LIKE N'%SpecialTable%' OR [sqlserver].[sql_text] LIKE N'%SpecialTable2%')
        AND [sqlserver].[database_name] = N'AdventureWorks'
    )
),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION (
        package0.event_sequence, package0.last_error, sqlos.worker_address, 
        sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname, 
        sqlserver.client_pid, sqlserver.compile_plan_guid, sqlserver.context_info, 
        sqlserver.database_name, sqlserver.execution_plan_guid, sqlserver.nt_username, 
        sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, 
        sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_nt_username, 
        sqlserver.session_server_principal_name, sqlserver.sql_text, sqlserver.transaction_sequence, 
        sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username
    )
    WHERE (
        ([sqlserver].[sql_text] LIKE N'%SpecialTable%' OR [sqlserver].[sql_text] LIKE N'%SpecialTable2%')
        AND [sqlserver].[database_name] = N'AdventureWorks'
    )
),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION (
        package0.event_sequence, package0.last_error, sqlos.worker_address, 
        sqlserver.client_app_name, sqlserver.client_connection_id, sqlserver.client_hostname, 
        sqlserver.client_pid, sqlserver.compile_plan_guid, sqlserver.context_info, 
        sqlserver.database_name, sqlserver.execution_plan_guid, sqlserver.nt_username, 
        sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, 
        sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_nt_username, 
        sqlserver.session_server_principal_name, sqlserver.sql_text, sqlserver.transaction_sequence, 
        sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username
    )
    WHERE (
        ([sqlserver].[sql_text] LIKE N'%SpecialTable%' OR [sqlserver].[sql_text] LIKE N'%SpecialTable2%')
        AND [sqlserver].[database_name] = N'AdventureWorks'
    )
)

ADD TARGET package0.event_file(SET filename=N'C:\Temp\AbeSQLMonitor.xel')
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
);
GO
-- Start the session immediately
ALTER EVENT SESSION [Monitor_OBJECTS] ON SERVER STATE = START;
-- Start automatically if the SQL Server restarts
ALTER EVENT SESSION [Monitor_OBJECTS] ON SERVER WITH (STARTUP_STATE=ON);
GO
Enter fullscreen mode Exit fullscreen mode

If you are running SQL Server on AWS RDS, you don't have access to standard directories, so you must save into the designated RDS log directory: D:\rdsdbdata\Log\
Once captured, you can read the data back using sys.fn_xe_file_target_read_file.
Also you can always filter your sessions by severity or exclude generic custom messages by filtering the error_number. Here is a full example combining all of these for aws rds. Errors equal or over 50000 are custom raise.

CREATE EVENT SESSION [SeekErrorsAWS] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.sql_text
    )
    WHERE (
        [package0].[greater_than_int64]([severity], (10)) 
        AND (
            [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'AdventureWorks') 
            OR [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'AdventureWorks2')) 
        AND [error_number] <> (50000)
    )
)
ADD TARGET package0.event_file(
    SET filename = N'D:\rdsdbdata\Log\SeekErrorsAWS',
    max_file_size = (32)
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = OFF
);
GO
ALTER EVENT SESSION [SeekErrorsAWS] ON SERVER STATE = START;
ALTER EVENT SESSION [SeekErrorsAWS] ON SERVER WITH (STARTUP_STATE=ON);
GO
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('D:\rdsdbdata\Log\SeekErrorsAWS*.xel', NULL, NULL, NULL);
Enter fullscreen mode Exit fullscreen mode

🫡 Scenario X: Mastering WHERE statements

So as you see crucial thing is your case and mastering WHERE statement.
You can also track sessions by duration, lets say you want to track sessions that take over 5 minutes to finish.

    WHERE (
        [duration] >= 300000000 
        AND [sqlserver].[database_name] = N'AdventureWorks'
    )
Enter fullscreen mode Exit fullscreen mode

or high logical reads, cpu, specific user..

WHERE (
    ([logical_reads] >= 10000 OR
    [cpu_time] >= 2000000 OR
    [sqlserver].[username] = N'SpecificUSER')
    AND [sqlserver].[database_name] = N'AdventureWorks'
)
Enter fullscreen mode Exit fullscreen mode

At the end, when you finish debug don't forget to stop or drop the event you have created.

-- Stop the sessions
ALTER EVENT SESSION [Seek_ERRORS] ON SERVER STATE = STOP;
ALTER EVENT SESSION [Monitor_OBJECTS] ON SERVER STATE = STOP;
GO

-- Drop the sessions
DROP EVENT SESSION [Seek_ERRORS] ON SERVER;
DROP EVENT SESSION [Monitor_OBJECTS] ON SERVER;
GO
Enter fullscreen mode Exit fullscreen mode

Reference:

Top comments (0)