26 performance tuning questions and solutions, Close(), GetNext(), How to tune SQL queries, Init(), Internal operator methods, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, Methods of Physical Operators, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, 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 interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, 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 Tips, SQL Tuning Overview, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, What are the internal methods of Physical Operators?, What are the internal methods of Physical Operators? How they work?
What are the internal methods of Physical Operators? How they work?
All the operators present in the execution plan are physical operators. We can see the physical operation by hovering over each operator in the execution plan.
Picture above showing example of physical operators in an execution plan
For example- Picture showing how each operator requests rows and how rows are returned. Please note that each operator asks for a row one at a time. So works like one record come at a time and goes out at a time.
All operators used in execution plans, implement three methods. They are
This is the first method for each physical operator.
It does following steps.
a. It initializes the physical operator.
b. It sets up the required data structures.
c. Some operators can receive more than one input, so, these inputs will be processed at the Init() method. The concatenation is one example of these operators.
a. This operator requests next record
b. It can have zero or more rows
c. It is responsible for setting Actual Rows property in the execution plan. Example below-
a. It is called once for each physical operator. It cleans up the things and shut down the operator.
So all in all what we have each operator asks for a row from his next operator and sucks it through. It works from left to right and the data flows from right to left.
That’s all folks; I hope you’ve enjoyed learning about physical operators and their internal methods, and I’ll see you soon with more “Performance Tuning” articles.
Pawan Kumar Khowal