The Parallelism Operator (aka Exchange)

Published Mar 23 2019 04:40 AM 564 Views
Microsoft
First published on MSDN on Oct 25, 2006

As I noted in my Introduction to Parallel Query Execution post , the parallelism (or exchange) iterator actually implements parallelism in query execution.  The optimizer places exchanges at the boundaries between threads; the exchange moves the rows between the threads.


The iterator that’s really two iterators


The exchange iterator is unique in that it is really two iterators: a producer and a consumer.  We place the producer at the root of a query sub-tree (often called a branch).  The producer reads input rows from its sub-tree, assembles the rows into packets, and routes these packets the appropriate consumers.  We place the consumer at the leaf of the next query sub-tree.  The consumer receives packets from its producer(s), removes the rows from these packets, and returns the rows to its parent iterator.  For example, a repartition exchange running at degree of parallelism (DOP) two, consists of two producers and two consumers:



Note that while the data flow between most iterators is pull based (an iterator calls GetRow on its child when it is ready for another row), the data flow in between an exchange producer and consumer is push based.  That is, the producer fills a packet with rows and “pushes” it to the consumer.  This model allows the producer and consumer threads to execute independently.  (We do have flow control to prevent a fast producer from flooding a slow consumer with excessive packets.)


How many different types of exchanges are there?


Exchanges can be classified in three different ways.


First, we can classify exchanges based on the number of producer and/or consumer threads:



Type# producer threads# consumer threads
Gather Streams DOP 1
Repartition Streams DOP DOP
Distribute Streams 1 DOP


A gather streams exchange is often called a “start parallelism” exchange since the operators above it run serially while the operators below it run in parallel.  The root exchange in any parallel plan is always a gather exchange since the results of any query plan must ultimately be funneled back to the single connection thread to be returned to the client.  A distribute streams exchange is often called a “stop parallelism” exchange.  It is the opposite of a gather streams exchange: the operators above a distribute steams exchange run in parallel while the operators below it run serially.


Second, we can classify exchanges based on how they route rows from the producer to the consumer.  We refer to this property as the “partitioning type” of the exchange.  Partitioning type only makes sense for a repartition or a distribute streams exchange since there is only one way to route rows in a gather exchange: to the single consumer thread.  SQL Server supports the following partitioning types:



Partitioning TypeDescription
Broadcast Send all rows to all consumer threads.
Round Robin Send each packet of rows to the next consumer thread in sequence.
Hash Determine where to send each row by evaluating a hash function on one or more columns in the row.
Range Determine where to send each row by evaluating a range function on one column in the row.  (A range function splits the total possible set of values into a set of continguous ranges.  This partition type is rare and is used only by certain parallel index build plans.)
Demand Send the next row to the next consumer that asks.  This partition type is the only type of exchange that uses a pull rather a push model for data flow and is used only in query plans with partitioned tables.


Third, we can classify exchanges as merging (or order preserving) and non-merging (or non-order preserving).  The consumer in a merging exchange ensures that rows from multiple producers are returned in a sorted order.  (The rows must already be in this sorted order at the producer; the merging exchange does not actually sort.)  A merging exchange only make sense for a gather or a repartition streams exchange; with a distribute streams exchange, there is only one producer and, thus, only one stream of rows and nothing to merge at each consumer.


Showplan


SQL Server includes all of the above properties in showplan (graphical, text, and XML).


Speaking of showplan, in graphical showplan you can also tell at a glance which operators are running in parallel (i.e., which operators are between a start exchange and a stop exchange) by looking for a little parallelism symbol on the operator icons:



In my next post about parallelism, I’ll begin to explore some parallel query plans and demonstrate the different types of exchanges in action.

%3CLINGO-SUB%20id%3D%22lingo-sub-383174%22%20slang%3D%22en-US%22%3EThe%20Parallelism%20Operator%20(aka%20Exchange)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383174%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Oct%2025%2C%202006%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EAs%20I%20noted%20in%20my%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fcraigfr%2Farchive%2F2006%2F10%2F11%2Fintroduction-to-parallel-query-execution.aspx%22%20mce_href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fcraigfr%2Farchive%2F2006%2F10%2F11%2Fintroduction-to-parallel-query-execution.aspx%22%20title%3D%22Introduction%20to%20Parallel%20Query%20Execution%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Introduction%20to%20Parallel%20Query%20Execution%20post%20%3C%2FA%3E%20%2C%20the%20parallelism%20(or%20exchange)%20iterator%20actually%20implements%20parallelism%20in%20query%20execution.%26nbsp%3B%20The%20optimizer%20places%20exchanges%20at%20the%20boundaries%20between%20threads%3B%20the%20exchange%20moves%20the%20rows%20between%20the%20threads.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20The%20iterator%20that%E2%80%99s%20really%20two%20iterators%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20exchange%20iterator%20is%20unique%20in%20that%20it%20is%20really%20two%20iterators%3A%20a%20producer%20and%20a%20consumer.%26nbsp%3B%20We%20place%20the%20producer%20at%20the%20root%20of%20a%20query%20sub-tree%20(often%20called%20a%20branch).%26nbsp%3B%20The%20producer%20reads%20input%20rows%20from%20its%20sub-tree%2C%20assembles%20the%20rows%20into%20packets%2C%20and%20routes%20these%20packets%20the%20appropriate%20consumers.%26nbsp%3B%20We%20place%20the%20consumer%20at%20the%20leaf%20of%20the%20next%20query%20sub-tree.%26nbsp%3B%20The%20consumer%20receives%20packets%20from%20its%20producer(s)%2C%20removes%20the%20rows%20from%20these%20packets%2C%20and%20returns%20the%20rows%20to%20its%20parent%20iterator.%26nbsp%3B%20For%20example%2C%20a%20repartition%20exchange%20running%20at%20degree%20of%20parallelism%20(DOP)%20two%2C%20consists%20of%20two%20producers%20and%20two%20consumers%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97240iBBFEB37A91D8CF3E%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ENote%20that%20while%20the%20data%20flow%20between%20most%20iterators%20is%20pull%20based%20(an%20iterator%20calls%20GetRow%20on%20its%20child%20when%20it%20is%20ready%20for%20another%20row)%2C%20the%20data%20flow%20in%20between%20an%20exchange%20producer%20and%20consumer%20is%20push%20based.%26nbsp%3B%20That%20is%2C%20the%20producer%20fills%20a%20packet%20with%20rows%20and%20%E2%80%9Cpushes%E2%80%9D%20it%20to%20the%20consumer.%26nbsp%3B%20This%20model%20allows%20the%20producer%20and%20consumer%20threads%20to%20execute%20independently.%26nbsp%3B%20(We%20do%20have%20flow%20control%20to%20prevent%20a%20fast%20producer%20from%20flooding%20a%20slow%20consumer%20with%20excessive%20packets.)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20How%20many%20different%20types%20of%20exchanges%20are%20there%3F%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EExchanges%20can%20be%20classified%20in%20three%20different%20ways.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFirst%2C%20we%20can%20classify%20exchanges%20based%20on%20the%20number%20of%20producer%20and%2For%20consumer%20threads%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3EType%23%20producer%20threads%23%20consumer%20threads%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3EGather%20Streams%3C%2FTD%3E%0A%20%20%20%20%3CTD%3EDOP%3C%2FTD%3E%0A%20%20%20%20%3CTD%3E1%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3ERepartition%20Streams%3C%2FTD%3E%0A%20%20%20%20%3CTD%3EDOP%3C%2FTD%3E%0A%20%20%20%20%3CTD%3EDOP%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3EDistribute%20Streams%3C%2FTD%3E%0A%20%20%20%20%3CTD%3E1%3C%2FTD%3E%0A%20%20%20%20%3CTD%3EDOP%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EA%20gather%20streams%20exchange%20is%20often%20called%20a%20%E2%80%9Cstart%20parallelism%E2%80%9D%20exchange%20since%20the%20operators%20above%20it%20run%20serially%20while%20the%20operators%20below%20it%20run%20in%20parallel.%26nbsp%3B%20The%20root%20exchange%20in%20any%20parallel%20plan%20is%20always%20a%20gather%20exchange%20since%20the%20results%20of%20any%20query%20plan%20must%20ultimately%20be%20funneled%20back%20to%20the%20single%20connection%20thread%20to%20be%20returned%20to%20the%20client.%26nbsp%3B%20A%20distribute%20streams%20exchange%20is%20often%20called%20a%20%E2%80%9Cstop%20parallelism%E2%80%9D%20exchange.%26nbsp%3B%20It%20is%20the%20opposite%20of%20a%20gather%20streams%20exchange%3A%20the%20operators%20above%20a%20distribute%20steams%20exchange%20run%20in%20parallel%20while%20the%20operators%20below%20it%20run%20serially.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESecond%2C%20we%20can%26nbsp%3Bclassify%20exchanges%20based%20on%20how%20they%20route%20rows%20from%20the%20producer%20to%20the%20consumer.%26nbsp%3B%20We%20refer%20to%20this%20property%20as%20the%20%E2%80%9Cpartitioning%20type%E2%80%9D%20of%20the%20exchange.%26nbsp%3B%20Partitioning%20type%20only%20makes%20sense%20for%20a%20repartition%20or%20a%20distribute%20streams%20exchange%20since%20there%20is%20only%20one%20way%20to%20route%20rows%20in%20a%20gather%20exchange%3A%20to%20the%20single%20consumer%20thread.%26nbsp%3B%20SQL%20Server%20supports%20the%20following%20partitioning%20types%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3EPartitioning%20TypeDescription%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3EBroadcast%3C%2FTD%3E%0A%20%20%20%20%3CTD%3ESend%20all%20rows%20to%20all%20consumer%20threads.%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3ERound%20Robin%3C%2FTD%3E%0A%20%20%20%20%3CTD%3ESend%20each%20packet%20of%20rows%20to%20the%20next%20consumer%20thread%20in%20sequence.%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3EHash%3C%2FTD%3E%0A%20%20%20%20%3CTD%3EDetermine%20where%20to%20send%20each%20row%20by%20evaluating%20a%20hash%20function%20on%20one%20or%20more%20columns%20in%20the%20row.%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3ERange%3C%2FTD%3E%0A%20%20%20%20%3CTD%3EDetermine%20where%20to%20send%20each%20row%20by%20evaluating%20a%20range%20function%20on%20one%20column%20in%20the%20row.%26nbsp%3B%20(A%20range%20function%20splits%20the%20total%20possible%20set%20of%20values%20into%20a%20set%20of%20continguous%20ranges.%26nbsp%3B%20This%20partition%20type%20is%20rare%20and%20is%20used%20only%20by%20certain%20parallel%20index%20build%20plans.)%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3EDemand%3C%2FTD%3E%0A%20%20%20%20%3CTD%3ESend%20the%20next%20row%20to%20the%20next%20consumer%20that%20asks.%26nbsp%3B%20This%20partition%20type%20is%20the%20only%20type%20of%20exchange%20that%20uses%20a%20pull%20rather%20a%20push%20model%20for%20data%20flow%20and%20is%20used%20only%26nbsp%3Bin%20query%20plans%20with%20partitioned%20tables.%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThird%2C%20we%20can%20classify%20exchanges%20as%26nbsp%3Bmerging%20(or%20order%20preserving)%20and%20non-merging%20(or%20non-order%20preserving).%26nbsp%3B%20The%20consumer%20in%20a%20merging%20exchange%20ensures%20that%20rows%20from%20multiple%20producers%20are%20returned%20in%20a%20sorted%20order.%26nbsp%3B%20(The%20rows%20must%20already%20be%20in%20this%20sorted%20order%20at%20the%20producer%3B%20the%20merging%20exchange%20does%20not%20actually%20sort.)%26nbsp%3B%20A%20merging%20exchange%20only%20make%20sense%20for%20a%20gather%20or%20a%20repartition%20streams%20exchange%3B%20with%20a%20distribute%20streams%20exchange%2C%20there%20is%20only%20one%20producer%20and%2C%20thus%2C%20only%20one%20stream%20of%20rows%20and%20nothing%20to%20merge%20at%20each%20consumer.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Showplan%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESQL%20Server%20includes%20all%20of%20the%20above%20properties%20in%20showplan%20(graphical%2C%20text%2C%20and%20XML).%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESpeaking%20of%20showplan%2C%20in%20graphical%20showplan%20you%20can%20also%20tell%20at%20a%20glance%20which%20operators%20are%20running%20in%20parallel%20(i.e.%2C%20which%20operators%20are%20between%20a%20start%20exchange%20and%20a%20stop%20exchange)%20by%20looking%20for%20a%20little%20parallelism%20symbol%20on%20the%20operator%20icons%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97241iB8A5BD176FD20B28%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20my%20next%20post%20about%20parallelism%2C%20I%E2%80%99ll%20begin%20to%20explore%20some%20parallel%20query%20plans%20and%20demonstrate%20the%20different%20types%20of%20exchanges%20in%20action.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383174%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Oct%2025%2C%202006%20As%20I%20noted%20in%20my%20Introduction%20to%20Parallel%20Query%20Execution%20post%2C%20the%20parallelism%20(or%20exchange)%20iterator%20actually%20implements%20parallelism%20in%20query%20execution.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383174%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerQueryProcessing%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 04:40 AM
Updated by: