Sunday, 16 July 2017

Query parsing or sequence tree in sql server

When we pass a T-SQL query to the SQL Server system, the first place it goes to is the relational engine. As the T-SQL arrives, it passes through a process that checks that the T-SQL is written correctly, that it's well formed. This process is query parsing. If a query fails to parse correctly, for example, if we type SELETC instead of SELECT, then parsing stops and SQL Server returns an error to the query source. The output of the Parser process is a parse tree, or query tree (or it's even called a sequence tree). The parse tree represents the logical steps necessary to execute the requested query.

If the T-SQL string is a DML statement and it has parsed correctly, the parse tree is passed to a process called the Algebrizer.
Each of these tasks is delegated to a separate component within the query processor; the Query Optimizer devises the plan and then passes it along to the Execution Engine, which will actually execute the plan and get the results from the database.


Parsing and binding

The query is parsed and bound. Assuming the query is valid, the output of this phase is a logical tree, with each node in the tree representing a logical operation that the query must perform, such as reading a particular table, or performing an inner join. This logical tree is then used to run the query optimization process, which roughly consists of the following two steps;

Generate possible execution plans

 Using the logical tree, the Query Optimizer devises a number of possible ways to execute the query i.e. a number of possible execution plans. An execution plan is, in essence, a set of physical operations (an index seeks, a nested loop join, and so on), that can be performed to produce the required result, as described by the logical tree;

Cost-assessment of each plan

While the Query Optimizer does not generate every possible execution plan, it assesses the resource and time cost of each plan it does generate. The plan that the Query Optimizer deems to have the lowest cost of those it’s assessed is selected, and passed along to the Execution Engine;

Query execution, plan caching


 The query is executed by the Execution Engine, according to the selected plan. The plan may be stored in memory, in the plan cache.

Popular Posts