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_complet

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

What you can do is enable Causality tracking

GUI

TSQL

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

and then collect everything and filter afterwards using the attach_activity_id.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.

StackExchange Database Administrators Q#312753, answer score: 7