1

I have following extended event in SQL Server 2012 to capture CXPACKET waits.

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

Extended Event

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
);
Community
  • 1
LCJ
  • 899
  • 2
  • 7
  • 29
  • 2
    Since DMVs only present information when queried, your best bet for getting all of the waits to really find this out would be to alter your extended events session above and capture all waits. Then load the data into a table and munge it around pulling out waits for sessions that had at least 1 CXPacket wait. Otherwise you'll be getting sampled data that may or may not reflect intermediate waits. – Sean Gallardy Sep 27 '16 at 14:50
  • Reference: https://dba.stackexchange.com/questions/126095/how-to-reduce-huge-cxpacket-latch-ex-access-methods-dataset-parent-wait-time – LCJ Nov 21 '17 at 19:16

0 Answers0