I have following extended event in SQL Server 2012 to capture
I need to find “other waits” also if the session has a CXPACKET, like the one achieved here using dynamic management view. What is the most light weight way to capture this information?
Also, how to display this information by querying this data aggregating on the thread level or execution_context_id (like total duration of wait for a thread)?
CREATE EVENT SESSION [MyEvent3] ON SERVER ADD EVENT sqlos.wait_info ( ACTION (sqlos.cpu_id, sqlserver.database_name, sqlserver.request_id, sqlserver.session_id, sqlserver.session_nt_username, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.username, package0.collect_system_time, package0.collect_cpu_cycle_time, --package0.callstack, sqlos.task_address, sqlos.worker_address ) WHERE ([wait_type] = 191) --CXPACKET --map_key value 187 in 2008 and 2008 R2 and 191 in 2012 and 2014 RTM. AND [database_name] = N'TestDB' ) ADD TARGET package0.event_file ( SET filename = N'G:\XE\event_session_test3_EF.xel', max_file_size = (20), max_rollover_files = (2) ) 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 = OFF, STARTUP_STATE = OFF );