This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The Storage Engine and Query Processor are both important core part of SQL Server. Storage Engine is responsible for data integrity and data storage in disk / memory while the Query Processor simply process the query bases on execution plan and return the result.
Most of the interviewer asks this question to SQL Server DBA that How SQL Server executes SQL Query and Statement so I have decided to prepare a short note on this.
How SQL Server internally executes SQL Query and Statement?
Parser:
The Parser makes sure that the T-SQL query has a valid syntax, and translates the SQL query into an initial logical tree representation with different information such as operators, join conditions, expressions, identifiers.
It sents this logical tree to Query Optimizer.
Binding:
It is a name resolution process and SQL Server makes sure that all object names are valid and they exist on a disk.
It creates algebrized tree with table and column names information and sents to Query Optimizer.
The Query Optimizer:
It uses logical tree and algebrized tree generated by Parsing and Binding Process and extracts the different information like, table data, table definition, database information and other database statistics.
If we look at specific table data extraction operation, there are different approaches like: selecting few records require Index Scan, selecting all table data require Table Scan, selecting data from multiple tables require parsing of JOIN conditions.
Query Optimizer is to find an efficient execution plan for your query. Even for relatively simple queries, there may be a large number of different ways to access the data to produce the same end result.
The Query Optimizer is a cost-based optimizer. Each execution plan has associated with cost estimation stats so After extracting the information, Query Optimizer chooses the best efficient execution plan and submit a query to query execution engine.
Query Optimizer is working within a time constraint, there’s a chance that the plan selected may be the optimal plan, but it is also likely that it may just be something close to the optimal plan.
Query Execution:
After optimization of the query, the Execution Engine selects resulting plan and retrieve the desired data.
It also stores generated execution plan into the memory for the query caching purpose.
The stored plan cache may reuse for same query execution, which actually saves lots of time of the Query Optimizer.
