In the previous article of this series, we discussed about the overall SQL server architecture and the Network Interface (specially). In this article we’ll discuss about the next, but very important component of SQL Server’s Database Engine i.e. the Relational Engine.
Also known as the Query Processor, the relational engine is responsible for the following functionalities of an RDBMS:
Parsing: Parsing is a general term which means transforming from one language to the other. In context to computers, parsing is the activity in which the user input (high level language) is converted to a machine understandable code (low level language). The figure 1.2 represents the function of a Command Parser of the Relation Engine.
Fig. 1.2 The Command Parser
In this phase, the parser checks for lexical and syntactic errors and if everything is fine, generates a Query Tree.
Optimization: The Optimizer is by far the most valuable asset of a database engine. This is the component which a particular RDBMS boasts of. The primary function of the optimizer is to generate execution plan. Execution plan refers to the plan which determines as to how to serve the user query.
Fig. 1.3 The Optimizer
Not all queries are sent for optimization. Queries like DDL commands are compiled into an internal form and the queries (mainly DML queries SELECT, INSERT, UPDATE and DELETE) that need to be optimized are marked first and then sent to the optimizer. The optimization process includes two steps: Normalization (query is broken down into simple fine-grained form) and Optimization.
Optimization is a Cost based process where the optimizer chooses the least costly plan. The cost factor is not based on time but on resource consumption viz. Memory requirement, CPU usage and no of I/Os needed.
The optimizer ensures (using pruning heuristics) that the time needed to optimize a given query tree and executing it does not exceed the time needed to simply pick an execution plan and execute the query.
The execution plan is actually a data structure, whose steps specify as to what objects are needed to execute it.
Another component called the SQL Manager takes care of the execution of stored procedures. It is responsible for deciding as to when a stored procedure needs recompilation and for storing the plan cache for reuse by other stored procedures. It also manages auto parameterization.
Query Execution: As the name suggests, this function is performed by the Query executer component of Relational Engine. It acts as the despatcher of the commands mentioned in the execution plan. The query executer works in association of the Storage engine, as each command needs to access the database.
Next: Storage Engine