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.
Mapping Physical and Logical Operators
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.
In This Section
This section contains descriptions of the following logical and physical operators:
|
See Also
Reference
Displaying Graphical Execution Plans (SQL Server Management Studio)
Concepts
Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)