Showplan - Logical and Physical Operators (Part -1)
Operators describe how SQL Server performs a query or a Data Manipulation Language (DML) statement. The query optimizer uses operators to shape 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 structure comprising of physical operators. You can view the query plan by using the SET SHOWPLAN statements. It is the graphical execution plan options in SQL Server Management Studio or the SQL Server Profiler Showplan event classes.
Operators are classified as logical and physical operators.
• Logical Operators
Logical operators describe the relational algebraic operation used to process a statement. Logical operators also describe conceptually what operation needs to be performed.
• Physical Operators
Physical operators implement the operation described by logical operators. Each physical operator is an object or routine that performs an operation. Let's say, some physical operators access columns or rows from a table, index or view. Other physical operators perform other operations such as calculations, aggregations, data integrity checks or joins. Physical operators have costs associated with them.
The query optimizer generates a query plan as a tree consisting of logical operators. After the query optimizer generates the plan, the query optimizer selects the most efficient physical operator for each logical operator. The query optimizer practices a cost-based approach to determine which physical operator will devise a logical operator.
Usually, a logical operation can be applied by multiple physical operators. However, in rare cases, a physical operator can implement multiple logical operations as well.
Operator Descriptions
This section contains descriptions of the logical and physical operators. I have documented nearly 112 logical as well as physical operators in the list.