This post is for T-SQL Tuesday #49, the monthly blog party of the community.
This tradition was started by Adam Machanic (blog | @AdamMachanic), and this month’s host is Robert David (blog | @SQLSoldier). This month’s topic is all about waits..
It’s Friday, Friday
It was Friday night.
As planned, I got home on time, got a few snacks and a drink, and turned on the TV just as the show was about to start. The performer took the stage, told a few jokes, and went to business.
What channel and show was it? The channel was PassTV, and the presentation was “Parallel Query Execution” by Paul White (blog | @SQL_Kiwi).
It was an excellent presentation. If you haven’t already watched it, you definitely should. Paul showed quite a few awesome things, but the thing I was most excited about was the ability to monitor a parallel query’s progress using CXPacket waits (at around 27:30 in the video).
This can serve two purposes:
- Give information about how far are we into execution of the query
- Give information about bottlenecks we have in the plan
I took the information I had learned in Paul’s presentation and used it to fix a complex query that was waiting for me at work. But before I explain how I did it, let’s talk about CXPacket.
What is a CXPacket Wait?
CXPacket stands for “Class Exchange Packets”. When rows are passed through parallelism operators (Distribute Streams, Repartition Streams and Gather Streams), also known as “Exchanges”, they do so in objects called “Packets”. A “Producer” thread fills the packet and sends it through the exchange, where a “Consumer” thread catches the packet. A CXPacket wait occurs when a consumer thread wants to get a packet but there is no packet ready for it, or when a producer thread wants to send a packet, but the CXPacket buffer is full.
When querying sys.dm_os_waiting_tasks at query run time and getting a CXPacket wait, looking at the resource_description column can show us some interesting information.
First, when seeing string ”e_waitPipeGetRow”, this means the consumer thread is waiting to get a packet, while when seeing the string “e_waitPipeNewRow”, it means the producer thread is the one waiting to pass a packet through the exchange.
Second, at the right of the column, we can see the Node Id. This is the ID of the exchange operator in the execution plan, and this basically implies the subtree that’s underneath it is currently being executed.
Detecting the Bottleneck
The query that was waiting for me at work was evil. It joined a few huge tables with a few nested views that queried a few more huge tables. As you can imagine, the execution plan tree was huge also.
I executed the query and started querying sys.dm_os_waiting_tasks, filtering it to show only the relevant session_id. I found the threads that waited on CXPacket and searched for the corresponding Node Ids in the execution plan.
As Paul explains in this post, I saw the execution flow starting to travel from left to right, and then the data started flowing back from right to left.
Two subtrees seemed to be working, but after 3 minutes, one of them finished and all of the threads were waiting for a single subtree to finish its work.
And then I realized this subtree is actually a complex view which makes the optimizer’s life much harder. Updating the statistics of the tables comprising this view didn’t help, so I turned to one of my favorite performance tuning techniques: I extracted the data from the view and put it in a temporary table, indexed that table, and used it in the query instead of the view.
This did the trick. The optimizer made a much better decision about the problematic subtree and performance was good again.
This powerful technique is yet another advantage of parallel plans over serial ones.
