WHAT HAPPENS WHEN A QUERY IS SUBMITTED?

When you submit a query to a SQL Server database, a number of processes on the server go to work on that query. The purpose of all these processes is to manage the system such that it will provide your data back to you, or store it, in as timely a manner as possible, whilst maintaining the integrity of the data.

All these processes go through two stages:

1. Relational Engine

2. Storage Engine

In the relational engine the query is parsed and then processed by the Query Optimizer, which generates an execution plan. The plan is sent (in a binary format) to the storage engine, which it then uses to retrieve or update the underlying data. The storage engine is where processes such as locking, index maintenance and transactions occur.

Relational Engine

            ||

              ||

              ||

Query Parsing

I/P: Given Query

Processing: It checks that the T-SQL is written correctly

O/P: Parse Tree \ Query Tree \ Sequence Tree

{IF IT IS DML GO TO NEXT ELSE GO TO STORAGE ENGINE}

           ||

             ||

             ||

Algebrizer

I/P: Parse Tree

Processing:

1. Verifies all the columns, objects and data types

2. Aggregate Binding (determines the location of aggregates

such as GROUP BY, and MAX)

O/P: Query Processor Tree in Binary Format

          ||

          ||

          ||

Query Optimizer

I/P: Query Processor Tree And Histogram (Statistics)

Processing: Finds an optimal way to execute the query

O/P: Cost Based Plan

||
|| ______ Stores in Cache Plan

||

||

_________________ |

||

||

||

Storage Engine

Once the execution plan is generated, the action switches to the storage engine, where the query is actually executed, according to the plan.