Extended events issue

  • 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:

    EE1

    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 1 week, 6 days ago by  as_1234. Reason: Typing error
  • 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
  • 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

  • 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.

     

  • 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

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

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