EXCHANGE operator

The EXCHANGE operator is a unary operator that encapsulates parallel processing of Adaptive Server SQL queries. EXCHANGE can be located almost anywhere in a query plan and divides the query plan into plan fragments. A plan fragment is a query plan tree that is rooted at an EMIT or EXCHANGE:EMIT operator and has leaves that are SCAN or EXCHANGE operators. A serial plan is a plan fragment that is executed by a single process.

An EXCHANGE operator’s child operator is always an EXCHANGE:EMIT operator. EXCHANGE:EMIT is the root of a new plan fragment. An EXCHANGE operator has an associated server process called the Beta process that acts as a local execution coordinator for the EXCHANGE operator’s worker processes. Worker processes execute the plan fragment as directed by the parent EXCHANGE operator and its Beta process. The plan fragment is often executed in a parallel fashion, using two or more processes. The EXCHANGE operator and Beta process coordinate activities, including the exchange of data between the fragment boundaries.

The topmost plan fragment, rooted at an EMIT operator rather than an EXCHANGE:EMIT operator, is executed by the Alpha process. The Alpha process is a consumer process associated with the user connection. The Alpha process is the global coordinator of all of the query plan’s worker processes. It is responsible for initially setting up all of the plan fragment’s worker processes and eventually freeing them. It manages and coordinates all of the fragment’s worker processes in the case of an exception.

The EXCHANGE operator displays this message:

Executed in parallel by N producer and P consumer processes.

The number of producers refers to the number of worker processes that execute the plan fragment located beneath the EXCHANGE operator. The number of consumers refers to the number of worker processes that execute the plan fragment that contains the EXCHANGE operator. The consumers process the data passed to them by the producers. Data is exchanged between the producer and consumer processes through a pipe set up in the EXCHANGE operator. The producer’s EXCHANGE:EMIT operator writes rows into the pipe while consumers read rows from this pipe. The pipe mechanism synchronizes producer writes and consumer reads such that no data is lost.

This example illustrates a parallel query in the master database against the system table sysmessages:

use master
go
set showplan on
go
select count(*) from sysmessages t1 plan '(t_scan t1)  (prop t1 (parallel 4))

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using the forced options (internally generated Abstract Plan).
Executed in parallel by coordinating process and 4 worker processes.

4 operator(s) under root

The type of query is SELECT.

ROOT:EMIT Operator
	|SCALAR AGGREGATE Operator
	|  Evaluate Ungrouped COUNT AGGREGATE.
	|
	|   |EXCHANGE Operator
	|   |Executed in parallel by 4 Producer and 1 Consumer processes.
	
	|   |
	|   |   |EXCHANGE:EMIT Operator
	|   |   |
	|   |   |   |SCAN Operator
	|   |   |   |  FROM TABLE
	|   |   |   |  sysmessages
	|   |   |   |  Table Scan.
	|   |   |   |  Forward Scan.
	|   |   |   |  Positioning at start of table.
	|   |   |   |  Executed in parallel with a 4-way hash scan.
	|   |   |   |  Using I/O Size 4 Kbytes for data pages.
	|   |   |   |  With LRU Buffer Replacement Strategy for data pages.

There are two plan fragments in this example. The first fragment in any plan, parallel or not, is always rooted by an EMIT operator. The first fragment in this example consists of the EMIT, SCALAR AGGREGATE, and EXCHANGE operators. This first fragment is always executed by the single Alpha process. In this example, it also acts as the Beta process responsible for managing the EXCHANGE operator’s worker processes.

The second plan fragment is rooted at the EXCHANGE:EMIT operator. Its only child operator is the SCAN operator. The SCAN operator is responsible for scanning the sysmessages table. The scan is executed in parallel:

Executed in parallel with a 4-way hash scan

This indicates that each worker process is responsible for approximately a quarter of the table. Pages are assigned to the worker processes based on having the data page ID.

The EXCHANGE:EMIT operator writes data rows to the consumers by writing to a pipe created by its parent EXCHANGE operator. In this example, the pipe is a four-to-one demultiplexer, and include several pipe types that perform quite different behaviors.