I made a small video that shows Logical Phases of Query Processing and published with the post called SQL Server Logical Phases of Query Processing. Not only logical phases, knowing Physical Phases would definitely help you on Query Optimization because it tells how SQL Server makes plans based on various rules and techniques. Let's try to understand the Physical Phases;
This is the first phase of Query Processing. This phase validates the query syntax and parses it into a Logical Query Tree. The sequence of items in the Logical Query Tree is based on Logical Phases describes with my above mentioned post. If SQL Server finds some syntax errors, then error is thrown without continuing. If no error found, then it goes to the next phase with the Logical Query Tree created. Note that this is also referred as Parse Tree and the component handles this is called as Command Parser.
This phase checks and see whether objects in the Logical Query Tree are exist and user has sufficient permissions on them. If objects are not exist or user has no permission, then it throws an error and stops the execution. If you see invalid object name error, it means that your query has stopped at Binding.
If everything works fine, then the output of this phase which is the Algebrizer Tree that is the Logical Query Tree bound to objects is created. This process is handled by Algebrizer.
- Query Optimization
The Query Optimizer is responsible for this phase. It takes the Algebrizer Tree and uses it with supportive components like Object Schema that are constraints, indexes and data types, and Object Statistics. This makes multiple plans with various steps and picks the best plan for the query.
- Query Execution
Finally Query Executor executes the query based on the plan created by Query Optimizer. Output of this phase is the result we expect.
Let's talk about more on Query Optimizer with the next post.