SQL University: Parallelism Week - Part 2, Query Processing

re: SQL University: Parallelism Week - Part 2, Query Processing

jamiet — Wed, 26 May 2010 12:52:51 GMT

Great stuff Adam, thanks for sharing this.

re: SQL University: Parallelism Week - Part 2, Query Processing

unclebiguns — Wed, 26 May 2010 14:21:29 GMT

Enjoying the series. Learning a lot about how parallelism works.

re: SQL University: Parallelism Week - Part 2, Query Processing

Paul White — Wed, 26 May 2010 16:17:38 GMT

Hey Adam,

Three things:

1. It's possible to read your description of repartitioning as saying that some schemes pass rows around, while others pass pages. The producer threads within an exchange iterator always push 'packets' of rows to consumer threads, regardless of the partitioning type (broadcast, round-robin, demand, hash).

2. Parallel scan does not use broadcast partitioning. It starts by threads requesting a row range or page set from a parallel page supplier in the storage engine. Once a thread finishes its initial work, it requests another set. This means that different threads can process a different number of rows (as you saw), which would not be possible if a strict round-robin scheme were used. Parallel scan uses this demand-based scheme to improve plan resilience in the face of data skew and/or scheduler load imbalances.

3. To be fair to parallel hash join, I would have mentioned that a hash partitioning distribution scheme results in a number (DOP) of hash tables which overall use the same amount of memory regardless of DOP. The other possibility for parallel hash join (broadcast) results in a memory grant requirement which is proportional to DOP.

Paul

re: SQL University: Parallelism Week - Part 2, Query Processing

Adam Machanic — Wed, 26 May 2010 16:44:29 GMT

Hi Paul,

Thanks for the feedback. I'll go back through later and clarify some of the text based on your comments (1) and (2), in addition to a quick re-read of some of Craig Freedman's material (should have done that BEFORE writing this stuff).

As for your comment (3), workspace memory grants are an additional complexity wrinkle (and quite a complex one at that) and I think would simply confuse things at this point in the series.

re: SQL University: Parallelism Week - Part 2, Query Processing

Paul White — Wed, 26 May 2010 16:51:21 GMT

Cheers Adam,

I see what you mean about point 3, but people get so down on parallelism already...I felt sorry for it I guess :)

Paul

re: SQL University: Parallelism Week - Part 2, Query Processing

James Luetkehoelter — Wed, 26 May 2010 20:07:46 GMT

Very nice stuff Adam!

re: SQL University: Parallelism Week - Part 2, Query Processing

Adam Belebczuk — Thu, 27 May 2010 13:27:36 GMT

Thanks for the awesome info Adam & Paul! :-)

SQL University: Parallelism Week - Part 3, Settings and Options

Adam Machanic — Fri, 28 May 2010 13:33:53 GMT

Congratulations! You've made it back for the the third and final installment of Parallelism Week here

SQL Sentry Plan Explorer : Version 1.1!

Aaron Bertrand — Wed, 22 Dec 2010 19:09:29 GMT

Last week, Microsoft offered up an early Christmas present: SQL Server 2005 SP4 . This week, it's SQL