Extended event - slow queries and waits



  • I have an extended event which filters on my slow queries. I have created the following script for it.

    CREATE EVENT SESSION [SlowQueriesAndStatementsLargerThan3Seconds] ON SERVER 
    ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
        WHERE ([duration]>=(3000000))),
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)
        WHERE ([duration]>=(3000000))),
    ADD EVENT sqlserver.sql_batch_completed(
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
        WHERE ([duration]>=(3000000)))
    ADD TARGET package0.event_file(SET filename=N'SlowQueriesAndStatementsLargerThan3Seconds'),
    ADD TARGET package0.ring_buffer
    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=ON,STARTUP_STATE=ON)
    GO
    

    I would like to include where my query is waiting for when it is being slow. I would like to include the wait_info for it, however when I add this, my trace fills up with unrelated waits for other queries. To trace the waits I've added the following event:

        CREATE EVENT SESSION [SlowQueriesAndStatementsLargerThan3Seconds] ON SERVER 
    ADD EVENT sqlos.wait_info(
        ACTION(sqlos.task_elapsed_quantum,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text)
        WHERE ([opcode]='End')),
    ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
        WHERE ([duration]>=(3000000))),
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)
        WHERE ([duration]>=(3000000))),
    ADD EVENT sqlserver.sql_batch_completed(
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
        WHERE ([duration]>=(3000000)))
    ADD TARGET package0.event_file(SET filename=N'SlowQueriesAndStatementsLargerThan3Seconds'),
    ADD TARGET package0.ring_buffer
    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=ON,STARTUP_STATE=ON)
    GO
    

    Is it possible to have the waits included for only the queries which ran for in this case 3 seconds or longer?



  • There is no way to use Causality tracking in the Extended Events collection filter.

    What you can do is enable Causality tracking

    GUI
    GUI Enable Causality tracking

    TSQL

    CREATE EVENT SESSION [TestEvent] ON SERVER 
    …
    WITH (TRACK_CAUSALITY=ON)
    

    and then collect everything and filter afterwards using the attach_activity_id.guid

    XE Output filter by guid

    which makes sense because you don't know the guid before so you cannot use it to filter. This way is usually not feasible due to how many wait_info events can be generated on a busy instance.

    Alternatively, you can enable Query Store with wait stats collection (starting with SQL Server 2017 (14.x))

    ALTER DATABASE 
    SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );
    

    The wait stats will be aggregated and grouped by category, but you can use Query Store to filter for a specific query hash or procedure name.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2