Stop Using Profiler: Precision SQL Tracing with Extended Events

The Death of Profiler

If you are still reaching for SQL Server Profiler every time you need to see what an application is doing, you are living in the past. Profiler is deprecated, heavy on resources, and often behaves like a sledgehammer when you need a scalpel. In a high-throughput production environment, running Profiler can noticeably degrade performance.

Extended Events (XEvents) is the modern, lightweight replacement. It operates deeper within the SQL Server engine, consuming significantly less overhead. More importantly, it allows for granular filtering that Profiler struggles to match. Today, we are going to look at the most common 'fire-drill' scenario: capturing every query executed by a specific service account or user login without crashing the server.

Why Filter by Login?

In a perfect world, your application logs would tell you exactly what SQL is being sent to the database. In the real world, we deal with legacy ORMs, third-party black-box software, and 'mystery' service accounts that are causing locking issues.

Capturing everything on a busy server is a suicide mission for your disk I/O. By filtering specifically on a nt_username or sqlserver.session_server_principal_name, we can isolate the noise and find the signal. This is essential for auditing, performance tuning, or debugging that one specific microservice that keeps hitting the database with unindexed queries.

The T-SQL Approach

While the SSMS GUI for Extended Events is functional, real DBAs use scripts. Scripts are repeatable, source-controllable, and faster to deploy. Below is the blueprint for a session that captures completed SQL statements for a specific login.

CREATE EVENT SESSION [CaptureUserSQL]
ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.nt_username)
    WHERE ([sqlserver].[session_server_principal_name] = N'YourTargetLogin')
),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.nt_username)
    WHERE ([sqlserver].[session_server_principal_name] = N'YourTargetLogin')
)
ADD TARGET package0.event_file(SET filename = N'C:\Temp\CaptureUserSQL.xel', max_file_size = (5), max_rolled_files = (2));
GO

Breaking Down the Events

In the script above, we are targeting two primary events: sql_statement_completed and sp_statement_completed.

1. sql_statement_completed: This catches ad-hoc T-SQL queries. If a dev is running SELECT * from their management studio or an ORM is sending raw strings, this is where they land.

2. sp_statement_completed: This is crucial. If you only track batch completion, you might miss individual statements inside a complex stored procedure. This event ensures you see exactly which line of the proc is the bottleneck.

We also attach 'Actions.' These are additional data points (like the hostname or app name) that aren't part of the default event payload but are vital for context. Note the filter: session_server_principal_name. This is the most reliable way to target a specific SQL or Windows login.

Managing the Output

I have set the target to event_file. You could use the ring_buffer (which keeps data in memory), but for any serious debugging, you want a file. The ring buffer is volatile and limited in size; if the server gets busy, you will lose the very events you were trying to catch.

The max_file_size and max_rolled_files parameters are your safety net. In this example, we limit the trace to two 5MB files. This ensures you don't accidentally fill up the C: drive on a production node—a mistake you only make once as a junior DBA.

Reading the Results

Once the session is running and the user has performed the actions you need to track, you can read the .xel files directly in SSMS by dragging them into the window. However, for large datasets, T-SQL is faster for analysis:

SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS sql_text,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(50)') AS host_name
FROM 
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\Temp\CaptureUserSQL*.xel', NULL, NULL, NULL)
) AS tab;

This query shreds the XML output into a relational format, allowing you to GROUP BY the SQL text to find the highest frequency queries or sort by duration to find the slow ones.

Final Thoughts

Extended Events are the gold standard for database observability. By targeting specific logins, you reduce the 'observer effect'—where the act of monitoring the system changes its performance. The next time someone asks you, 'What is this service account doing?', don't fire up Profiler. Deploy a targeted XEvent session, get the data, and get out. Your server's CPU will thank you.