Extended events issue

Question

Extended events issue

as_1234

SSCrazy

Points: 2865
More actions
July 10, 2024 at 8:08 am

#4434777
I have this extended event set up:
```
CREATE EVENT SESSION [Performance monitoring] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle)
WHERE ([package0].[greater_than_int64]([duration],(30000)) AND [package0].[not_equal_unicode_string]([sqlserver].[session_nt_user],N'NT SERVICE\SQLTELEMETRY')))
ADD TARGET package0.event_file(SET FILENAME=N'...',max_file_size=(5))
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
```
The issue I'm having is that it's not excluding results for the SQLTelemetry account as shown in the example below:
Does anyone know how I can make the exclusion work? I think it might have something to do with the fact that the output shows 'NT_username' and the filter uses 'session_nt_user'. I've tried various combinations of the username fields with the same result.
I'm aware it might be an unusual event to set it up. I'm doing some experimenting/learning on a dev system. I'm intending to compare the results to some other extended events I have set up.
Thanks
- This topic was modified 5 months, 2 weeks ago by as_1234. Reason: Typing error

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

try

CREATE EVENT SESSION [Performance monitoring] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle)
WHERE ([package0].[greater_than_int64]([duration],(30000)) AND [sqlserver].[username]<>N'NT SERVICE\SQLTELEMETRY'))
ADD TARGET package0.event_file(SET FILENAME=N'...',max_file_size=(5))
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

This reply was modified 5 months, 2 weeks ago by frederico_fonseca.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Yeah, I was thinking something similar. I wonder if it's the unicode string that's causing the issue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

as_1234 SSCrazy Points: 2865 More actions · Answer 3

All,

Thanks for your help. That seems to have fixed the issue.

I thought I'd already tried <> rather than the unicode option with all the different variations of the username field but I must have missed a combination.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Happens to everyone. No worries.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning