5

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'?

4

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.

  • The primary motivation wasn't performance tuning but need to find all the queries with nolock query hint, because they led sometimes into error: "A read operation on a large object failed while sending data to the client.". In fact ex event definition was built upon MS Support Engineer recommendation for classic sql trace - I just rewrote it into ex events (agree that in this case I would omit starting events as well) and was supersuprised by the performance impact.I have more very bad experience with [like_i_sql_unicode_string] and I am curious if others use it without problems. – dewet Jul 25 '14 at 7:30
  • In fact I do believe that catching all the events (and then filtering them out during analysis) would have much smaller performance impact then filtering them during processing... – dewet Jul 25 '14 at 7:32
  • 1
    sp_statement_starting and sp_statement_completed are going to be very intense on any busy server. Remove these. If you want to search stored procs for hints, just use sys.sql_modules. Certain coding patterns ( eg scalar functions in SELECT clause ) are very sensitive to this level of tracing and will also generate a lot of volume. – wBob Sep 18 '14 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.