Create NOT LIKE filter for Extended Events Session

_{Thursday, June 27, 2019 3:17 PM}

I'm making the transition from SQL Profiler to Extended Events. I've successfully created sessions and captured performance data. Now I'd like to learn how to filter the session so that I don't capture more data than necessary.

What's frustrating me is that there does not appear to be a "NOT LIKE" operator for either "statement" or "sql_text". I'm used to creating filters that screen out data I'm not interested in. For example "[sql_text] NOT LIKE '%SERVERPROPERTY%'"

Any ideas about how to screen out things like SQL Server system processes without using "NOT LIKE"?

_{Thursday, June 27, 2019 3:47 PM ✅Answered}

What's frustrating me is that there does not appear to be a "NOT LIKE" operator for either "statement" or "sql_text". I'm used to creating filters that screen out data I'm not interested in. For example "[sql_text] NOT LIKE '%SERVERPROPERTY%'"

Any ideas about how to screen out things like SQL Server system processes without using "NOT LIKE"?

I think this is just a limitation of the current SSMS UI. If you script the definition DDL, you can tweak the script to add a NOT before the LIKE expression predicate. For example filtering the batch_text of the sql_batch_completed event:

CREATE EVENT SESSION [Example] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([batch_text],N'%SERVERPROPERTY%')));
GO

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

_{Friday, June 28, 2019 12:06 PM}

Ok, let me try that. My plan was to create one filter and then script it out so I could see how to format the SQL. Thanks.

_{Friday, June 28, 2019 2:11 PM}

After a little more exploration, I see the filters are a little more complicated. "batch_text" only applies to the "sql_batch_completed" event. Other events have different fields I will have to filter on.

_{Friday, June 28, 2019 5:45 PM}

Some final notes for those who stumble upon this thread in the future:

• I found the best attribute to filter on is "[sqlserver].[sql_text]", as this attribute exists on most of the events I am using
• Each event can have it own filters
• The total number of characters allowed for ALL filters on an event is 3,000, as specified by the following error message:

Msg 25716, Level 16, State 1, Line 1
The predicate on event, "sqlserver.attention", exceeds the maximum length of 3000 characters.

Definitely learned a lot from this experience. Thanks to Dan for this assistance.

_{Saturday, June 29, 2019 11:43 AM}

Thanks for sharing you experiences. Adding that one can also create separate traces of fewer/different event types to workaround some of these limitations.

Dan Guzman, Data Platform MVP, http://www.dbdelta.com