Export (0) Print
Expand All
Expand Minimize

Logical and Physical Operators Reference

Operators describe how Microsoft SQL Server executes a query or a Data Manipulation Language (DML) statement. The query optimizer uses operators to build a query plan to create the result specified in the query, or to perform the operation specified in the DML statement. The query plan is a tree consisting of physical operators.

Operators are classified as logical and physical operators. Logical operators describe a relational query processing operation on a conceptual level. Physical operators actually implement the operation defined by a logical operator using a concrete method or algorithm. For example, "join" is a logical operation, whereas "nested loops joins" is a physical operator.

Logical Operators

Logical operators describe the relational algebraic operation used to process a statement. In other words, logical operators describe conceptually what operation needs to be performed.

Physical Operators

Physical operators implement the operation described by logical operators. Each of the physical operators is an object or routine that performs an operation. For example, some physical operators access columns or rows from a table, index or view. Other physical operators perform other types of operations: The Aggregate operator calculates an expression containing MIN, MAX, SUM, COUNT or AVG, and the Merge Join operator performs different types of logical join operations.

The physical operators initialize, collect data, and close. Specifically, the physical operator can answer the following three method calls:

  • Init(): The Init() method causes a physical operator to initialize itself and set up any required data structures. The physical operator may receive many Init() calls, though typically a physical operator receives only one.
  • GetNext(): The GetNext() method causes a physical operator to get the first, or subsequent row of data. The physical operator may receive zero or many GetNext() calls.
  • Close(): The Close() method causes a physical operator to perform some clean-up operations and shut itself down. A physical operator only receives one Close() call.

The GetNext() method returns one row of data, and the number of times it is called appears as ActualRows in the Showplan output that is produced by using SET STATISTICS PROFILE ON or SET STATISTICS XML ON. For more information about these SET options, see SET STATISTICS PROFILE (Transact-SQL) and SET STATISTICS XML (Transact-SQL).

The ActualRebinds and ActualRewinds counts that appear in Showplan output refer to the number of times that the Init() method is called. Unless an operator is on the inner side of a loop join, ActualRebinds equals one and ActualRewinds equals zero. If an operator is on the inner side of a loop join, the sum of the number of rebinds and rewinds should equal the number of rows processed on the outer side of the join. A rebind means that one or more of the correlated parameters of the join changed and the inner side must be reevaluated. A rewind means that none of the correlated parameters changed and the prior inner result set may be reused.

ActualRebinds and ActualRewinds are present in XML Showplan output produced by using SET STATISTICS XML ON. They are only populated for the Nonclustered Index Spool, Remote Query, Row Count Spool, Sort, Table Spool, and Table-valued Function operators. ActualRebinds and ActualRewinds may also be populated for the Assert and Filter operators when the StartupExpression attribute is set to TRUE.

When ActualRebinds and ActualRewinds are present in an XML Showplan, they are comparable to EstimateRebinds and EstimateRewinds. When they are absent, the estimated number of rows (EstimateRows) is comparable to the actual number of rows (ActualRows). Note that actual graphical Showplan output displays zeros for the actual rebinds and actual rewinds when they are absent. For more information about graphical Showplans, see Displaying Graphical Execution Plans (SQL Server Management Studio).

A related counter, ActualEndOfScans, is available only when Showplan output is produced by using SET STATISTICS XML ON. Whenever a physical operator reaches the end of its data stream, this counter is incremented by one. A physical operator can reach the end of its data stream zero, one, or multiple times. As with rebinds and rewinds, the number of end of scans can be more than one only if the operator is on the inner side of a loop join. The number of end of scans should be less than or equal to the sum of the number of rebinds and rewinds.

Physical operators correspond to execution algorithms. Examples of physical operators include index scan/seek, nested loop join, merge join, hash join/aggregation, stream aggregation. Physical operators have costs associated with them. Each step in the execution of a query or DML statement involves a physical operator.

The query optimizer creates a query plan as a tree consisting of logical operators. After the query optimizer creates the plan, the query optimizer chooses the most efficient physical operator for each logical operator. The query optimizer uses a cost-based approach to determine which physical operator will implement a logical operator.

Usually, a logical operation can be implemented by multiple physical operators. However, in rare cases, a physical operator can implement multiple logical operations as well.

This section contains descriptions of the following logical and physical operators:

Aggregate

Log Row Scan

Arithmetic Expression

Merge Interval

Assert

Merge Join

Assign

Nested Loops

Async Concat

Nonclustered Index Update

Bitmap

Nonclustered Index Spool

Bitmap Create

Nonclustered Index Seek

Bookmark Lookup

Nonclustered Index Scan

Branch Repartition

Nonclustered Index Insert

Broadcast

Nonclustered Index Delete

Cache

Online Index Insert

Clustered Index Delete

Parallelism

Clustered Index Insert

Parameter Table Scan

Clustered Index Scan

Partial Aggregate

Clustered Index Seek

Population Query

Clustered Index Update

Refresh Query

Collapse

Remote Delete

Compute Scalar

Remote Insert

Concatenation

Remote Query

Constant Scan

Remote Scan

Convert

Remote Update

Cross Join

Repartition Streams

Cursor Catchall

Result

Declare

RID Lookup

Delete

Right Anti Semi Join

Deleted Scan

Right Outer Join

Distinct

Right Semi Join

Distinct Sort

Row Count Spool

Distribute Streams

Segment

Dynamic

Segment Repartition

Eager Spool

Sequence

Fetch Query

SequenceProject

Filter

Snapshot

Flow Distinct

Sort

Full Outer Join

Split

Gather Streams

Spool

Hash Match

Stream Aggregate

Hash Match Root

Switch

Hash Match Team

Table Delete

If

Table Insert

Inner Join

Table Scan

Insert

Table Spool

Inserted Scan

Table Update

Intrinsic

Table-valued Function

Iterator Catchall

Top

Key Lookup

Top N Sort

Keyset

UDX

Language Element Catchall

Union

Lazy Spool

Update

Left Anti Semi Join

While

Left Outer Join

Cursor Logical and Physical Showplan Operators

Left Semi Join

 

Community Additions

ADD
Show:
© 2014 Microsoft