26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to handle large number of insertions in SQL Server?, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, Operators in SQL SERVER, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, Spools in Execution Plan?, Spools in SQL Server, sql - How do I avoid functions like UPPER in where clause, SQL - Spools, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, SQL Performance Tuning and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server - Logical and Physical Operators, SQL Server 2008 Query Performance Tuning Distilled, SQL Server Interview Question - How to handle large number of insertions in SQL Server?, SQL SERVER Interview questions, SQL Server Optimization - Parallel Execution Plans, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL Server Query Optimization: Parallel Plans, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?
SQL Server – Logical and Physical Operators
Operators describe how SQL Server executes a query or a Data Manipulation Language (DML) statement.
The query optimizer uses these operators to build an execution 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. You can check these operators in the execution plan.
Types of Operators are-
1. Logical Operators
2. Physical 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 implement the operation described by logical operators. Each physical operator 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 operations such as calculations, aggregations, data integrity checks or joins. Physical operators have costs associated with them.
The physical operators initialize, collect data, and close. Specifically, the physical operator can answer the following three method calls
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.
Example of Logical and Physical Operator
SELECT TOP 10 ID, FromPoint, ToPoint, Distance FROM [dbo].[Area]
Check out the execution plan for Logical and Physical Operators
Right click on each operator and click properties to check out the logical and physical operations.
In the next post i will explain how a query executes in SQL Server, What are the internal steps used by Query Optimizer. Cheers, Thanks for reading !
You must be logged in to post a comment.