Decoding the Silence: Fixing Non-Working SQL Extended Events

Today, I’m sharing a problem I’ve just run into with MSSQL Extended Events. This tool is supposed to help us understand and fix problems, but sometimes it doesn’t work right. I was dealing with a tough performance problem and when I turned to Extended Events for answers, they didn’t work as they should. Stick with me as I talk about what went wrong.

Problem I Ran Into

I was working with SQL Server 2022 (16.0.4115.5 CU12) and set up an Extended Event session because I needed to collect some diagnostic data. I used the graphical interface to create the session and let it run for a few hours. When I checked back, to my surprise, there was nothing recorded — the trace was completely empty. This was definitely not what I had expected since everything was set up correctly… or so I thought.

Here’s a sample of the session that’s been pared down to capture just a single event, with no filters. I simplified it this way to make the issue easier to spot during troubleshooting.

Here’s the script you can use to create the Extended Event (XE) session:

CREATE EVENT SESSION [Error_err] ON SERVER ADD EVENT sqlserver.error_reported (ACTION(sqlos.task_time, SQLSatellite.AppName, sqlserver.client_hostname, sqlserver.database_name, sqlserver.query_hash_signed, sqlserver.query_plan_hash_signed, sqlserver.session_id, sqlserver.sql_text, sqlserver.username)) ADD TARGET package0.event_file (SET filename = N'Error_err.xel')
	WITH (
			MAX_MEMORY = 8192 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
			)

After setting up, I started the session and conducted a test to capture data by raising an error on purpose, but there was nothing at all in file:

RAISERROR ('Error raised for XE test.', 16, 1);

Identifying the Problem

Turns out, I had a little mix-up with the global fields. In the heat of the moment, I chose ‘AppName’ instead of ‘client_app_name’. Honestly, I would have expected to get NULL values in the trace if the field was wrong/blank, not an empty trace altogether. Once I switched off ‘AppName’ and turned on ‘client_app_name’, everything worked like a charm, and the session started capturing data without any further issues.

Conclusion

The 2022 version of SQL Server brought in some new global fields in the Extended Events session interface, including ‘AppName’. This new addition can easily be confused with the existing ‘client_app_name’ field. If you accidentally activate ‘AppName’, you might end up with some unexpected results when trying to collect data.