Capture CXPACKET Wait Using Extended Events – SQL 2012


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.

Advertisements

2 thoughts on “Capture CXPACKET Wait Using Extended Events – SQL 2012

  1. CXPACKET isn’t a problematic wait type, it just tells you that you have parallelism occurring. You need to know the root wait type that lead to the CXPACKET waits occurring on the suspended threads to diagnose why you have CXPACKET waits.

    • Hi Jonathan,

      Thanks a lot for your comment.As you said,I understand that CXPACKET wait is not always a problematic wait type and its more often a symptom of another underlying problem.

      I will update this post by adding your feedback and will also let readers know that they can monitor the queries which are having using parallelism and check why its happening.

      Thanks again for reading this post and providing your valuable feedback.It matters a lot.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s