Background
We use the select query in sql server to retrive the records from the table ,at that time lots of process are done that we can know,So in this blog i will explain How the Select query is Run ?
Introduction
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:
- Parsing
- Normalization
- Compilation
- Optimization
Parsing:
During this stage, SQL Server checks the query
for Syntax errors and transforms it into a complier-ready structure that it
will use later to optimize the query. It does not check for object names or
column names.
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.
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.
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 .