PageViews Last Month

Friday 11 September 2015

What happens when you execute a query

When a query is submitted to SQL server it goes through several processing steps. A proper understanding of these steps is essential as they explain how the relational engine did what it was asked to do via the query.

Step 1: Parsing  

This is the first step performed during query execution. This involves syntax checks. A statement that breaks a rule for well-formed SQL syntax fails the check. Output of this step is a parse tree.

Step 2: Binding

The parsed tree is taken as input by the Algebrizer for binding. The algebrizer performs a semantic check to determine whether a statement is meaningful, for example, whether the objects and columns in the statement exist. Algebrizer also identifies all data types which are being processed for a given query. After this step the statement is no longer available in source format when the command is actually executed. Algebrizer produces a query processor tree, which works as input for query optimizer.

Step 3 : Query Optimization 

The query optimization is the last step of compilation. Statements that can’t be optimized, such as flow-of-control and Data Definition Language (DDL) commands, are compiled into an internal form. The statements that are optimizable are marked as such and then passed to the Query Optimizer. The job of the Query Optimizer is to take the query tree that was output from the algebrizer and find a “good” way to retrieve the data (results) needed. SQL Server uses a cost based optimizer. Cost is calculated primarily by considering the size of data that would have to be read by each alternative. In order to come up with these costs SQL Server needs to know the size of each table and the distribution of column values, which is are available from the statistics associated with the data. The output of the optimizer is a query execution plan.

Step 4 : Query Execution

This is the last step of query processing. As the steps that require data from the base tables are processed, the relational engine requests that the storage engine pass up data from the row sets requested from the relational engine.