Its been long that I wanted to capture Wait Stats using extended events session,and today I got a chance to demonstrate the same.

The focus was to capture CXPACKET wait stats which comes with parallelism.

I have been planning to setup a demo to simulate CXPACKET wait type,however this blog post pulled my attention.This post was talking about AND and OR operators,execution plans and optimization techniques.

The above post was pretty nice and the script which he used was actually invoking parallelism and this grabbed my attention to check if I can capture CXPACKET wait using extended events using the same script.

I dont want to repeat the code,and you can refer the same from the original post which is available here.I am using just the first 3 parts of the script,ie Create the database,Insert 1 million records and do a Select which has a predicate and an operator.

The table structure is

1 million records have been already inserted to the table.

Before querying the table for results,lets create an Extended Events session

   1:  CREATE EVENT SESSION [Check CXPACKETWait] ON SERVER
   2:  ADD EVENT sqlos.wait_info
   3:      (
   4:      ACTION (sqlos.cpu_id,
   5:              sqlserver.database_name,
   6:              sqlserver.request_id,
   7:              sqlserver.session_id,
   8:              sqlserver.session_nt_username,
   9:              sqlserver.sql_text,
  10:              sqlserver.transaction_id,
  11:              sqlserver.username)
  12:      WHERE ([wait_type] = (191))
  13:      )
  14:  WITH
  15:  (
  16:          MAX_MEMORY = 4096 KB,
  17:          EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
  18:          MAX_DISPATCH_LATENCY = 30 SECONDS,
  19:          MAX_EVENT_SIZE = 0 KB,
  20:          MEMORY_PARTITION_MODE = NONE,
  21:          TRACK_CAUSALITY = OFF,
  22:          STARTUP_STATE = OFF
  23:  );

We have the option to use SSMS to create the session too and configure the event.Using SSMS we can select the Global Fields(Actions) which needs to be captured along with a predicate(Filter).

The name of the session which we configured is Check CXPACKETWait with global fields cpu_id,database_name,request_id,session_id,session_nt_username,sql_text,

transaction_id,usernameand predicate filter = CXPACKET.

Now as we have the event session created,we can start the session.

Lets now do a Select query to the table which was created earlier

   1:  SELECT Id,
   2:         Name
   3:  FROM   Inventory.Products
   4:  WHERE  Status = 7
   5:         AND Weight BETWEEN 500.00 AND 700.00
   6:         AND ListPrice < $5.00;

The execution plan for the above query clearly talks about parallelism and also gives some suggestion on index.

We had already started the event session and with SQL 2012 we have the option to watch LIVE data and if you check the data,you can see the events which are captured.

Conclusion

Extended events will give the flexibility to troubleshoot wait related issues and this is pretty much flexible too.

[Update Added on 4/4/2012]

I would strongly advise you to read the comment section of this blog post where Jonathan Kehayias(blog/twitter) provided his feedback about CXPACKET wait type.

As mentioned by him CXPACKET wait type is not always a problem and we should not be trying to change MDOP value or Cost threshold of parallelism without proper analysis.CXPACKET wait is mostly a symptom of an underlying problem.

We have to analyse the queries which are using parallelism and try to understand why there are using it.A clear analysis will help you to find if using parallelism is really beneficial.

[Update]

Thanks for reading.