Sql-server – Performance impact of Extended Events filter [like_i_sql_unicode_string]

extended-eventssql serversql-server-2012

I have created an extended events session in which I wanted to see all queries using nolock hint. I have decided to use it instead of sql trace in order to minimaze possible performance impact. Session was created like with following statement:

    CREATE EVENT SESSION [SCOMDB_debug] ON SERVER 
    ADD EVENT sqlserver.deprecation_announcement(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)),
    ADD EVENT sqlserver.rpc_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.rpc_starting(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sp_statement_starting(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_batch_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_batch_starting(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_statement_starting(SET collect_statement=(1)
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))) 
    ADD TARGET package0.event_file(SET filename=N'M:\MSSQL11.SCOMDB\MSSQL\Log\SCOMDB_debug.xel',max_file_size=(500))
    WITH (MAX_MEMORY=40960 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 result was that database instance (used for SCOM databases) became very slow and application was almost unusable.

Wait stats were full of SOS_SCHEDULER_YIELD and PREEMPTIVE_XE_DISPATCHER and I could see many long running queries in sys.dm_exec_requests waiting mostly on CPU as well.

Is it expecated bahaviour? I can imagine that [like_i_sql_unicode_string] operator can be quite performance consuming but still I would expect much better performance.

Is there any other filter in ex events which could be used to catch queries which contain 'sometext'?

Best Answer

I would say you don't need all the start events, just the completed. I don't see that you are capturing data just for the SCOM database either. Extended events can cause an overhead just near to what SQL Server traces will depending on what you are trying to capture.

You are basically capturing every query that comes across the server and telling it to wait so you can first check the text of the query and then grab information through the extended event session, then letting it complete.

As well the constant activity SCOM has, depending on environment configuration, it is likely to have an extreme performance hit either with trace or extended events. I would suggest looking at the plan cache for those types of queries initially. However you might try adding more filters to your session such only grabbing a sample of data from active sessions and then also only capturing data for the particular database. That would look something like this through the GUI:

enter image description here

Or code your WHERE clause would look something like:

WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5))) 
   AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)))) 
   AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))

This would capture I think 20% of the active sessions for that database at any given time. However if you want all events then you would have to suffer the overhead of that action.

With it being a Microsoft product I would not be to concerned with what queries it is producing. If it is experiencing performance issues you should follow Microsoft guidelines for the product or consult Microsoft support.

Related Question