SQL Server performs
a couple of internal steps before executing a query. The steps that interest us
here are compilation and execution.
When SQL Server
receives a query for execution, its execution plan should already be present in
memory (the procedure cache); if not, SQL Server will have to compile the query
before executing it.
The compilation
process is divided into four parts:
1. Parsing
2. Normalization
3. Compilation
4. Optimization
Parsing:
During this stage,
SQL Server checks the query for Syntax errors and transforms it into a
compiler-ready structure that it will use later to optimize the query. It does
not check for object names or column names.
Normalization:
Normalization:
At this stage, SQL
Server checks all references to objects in the query. This is where we
typically get the “Object not found” message when an object referenced in the
query is not found in the database. SQL Server also checks to see if a query
makes sense. For example, we cannot execute a table or select from a stored
procedure.
Bear in mind that while we can optimize select, insert, and update statements, there is no way to optimize if, while, and for operators.
Bear in mind that while we can optimize select, insert, and update statements, there is no way to optimize if, while, and for operators.
Compilation:
This is where we start building the execution plan for the query we passed to SQL Server. First, we create a sequence tree. The sequence tree is normalized, again, which includes adding implicit conversions if necessary. Also during this phase, if the query is referencing views, a view definition is placed in the query. If a statement is a DML statement, a special object is created called the query graph. The query graph is the object on which the optimizer works to generate an optimized plan for the query. This is the compiled plan that is stored in the procedure cache for reuse.
This is where we start building the execution plan for the query we passed to SQL Server. First, we create a sequence tree. The sequence tree is normalized, again, which includes adding implicit conversions if necessary. Also during this phase, if the query is referencing views, a view definition is placed in the query. If a statement is a DML statement, a special object is created called the query graph. The query graph is the object on which the optimizer works to generate an optimized plan for the query. This is the compiled plan that is stored in the procedure cache for reuse.
Optimization:
SQL Server
Optimizer is a cost-based optimizer, which means that it will come up with the
cheapest execution plan available for each SQL statement. For each SQL
statement to run we need to use resources like CPU, memory, hard disk, etc. The
cheapest plan is the one that will use the least amount of resources to get the
desired output. For optimizing DML statements, SQL Server will test different
indexes and join orders to get the best plan for executing the query. Your
index definition helps optimizer by reducing/minimizing resource usage. If the
index has a high selectivity then it is most suitable for optimization. Because
a complex query will take into account all indexes and joins, there can be many
paths to take to execute the query. In such cases, determining the best path
for optimization can take a long time. The longer this process takes, the higher
the cost that is involved.
So first, a trivial
plan is generated. This plan assumes that cost-based optimization is costly; if
there is only one path for execution possible, there is no point optimizing the
query. For example, when placing a simple insert statement into a table, there
is no way that your indexes or join orders can increase optimization, so the
trivial plan is used.
Summary
I hope this blog is
useful all readers, if you have any suggestion then contact me.
Post a Comment