Working Of Select Query in Sql Server

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 .