Tag: like_i_sql_unicode_string

MSSQL SERVER: Create an Extended Event Session with a “NOT LIKE” filter criteria on sql_statement_completed

As of the writing of this post, when creating extended events sessions in SQL Server Management Studio (SSMS), on the Set Session Event Filters page of the New Session Wizard, there is an option to include events that are like a specified value (using the like_i_sql_unicode_string operator) but there is not currently anything in the GUI to EXCLUDE queries that meet this filter criteria.

Adding a “NOT LIKE” clause to the extended events filter seems to be a limitation of the GUI currently. The steps below show you how to select all of your extended events via the wizard and then add the “NOT LIKE” functionality at the end by scripting out the Extended Event and making a minor tweak.

Open the New Session Wizard in SSMS:

Give it a name

Select “Do not use a template” and click Next

Search for sql_statement_completed in the list and add it to the Selected Events and click Next

On the Capture Global Fields page, click Next

On the Set Session Event Filters page, select “sqlserver.sql_text from the “Field” drop down list and set the “Operator” to like_i_sql_unicode_string and set the “value” field to the full or partial sql query that you would like to exclude. Then click next.

Continue to the Summary page of the wizard and click on the “Script” button

You should see a script like this:

We’re almost there but this script in its current form will do the opposite of what we want. This script will capture ONLY the sp_BlitzFirst execution. To have it exclude these executions, we have to make the following change:

Here is the TSQL text from this proof of concept:

CREATE EVENT SESSION [XEventFilterTest] ON SERVER

ADD EVENT sqlserver.sql_statement_completed(

    WHERE ( NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'EXEC sp_BlitzFirst%')))

WITH (STARTUP_STATE=OFF)

GO

Other Notes:

The total number of characters allowed for ALL filters on an event is 3,000. If you execeed this threshold, you may see an error that resembles the following:

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

To workaround this issue, split your Extended Events Session into multiple sessions.