SQL - Basics Sql execution plan

Below given is high level diagram shows how Sql queries get executed

Sql query processing steps : There are some steps for sql statements execution

1. Parsing : Parser checks syntax of sql statement including spellings, reserved keywords etc, and prepare sequence tree.
2. Normalization : Normalization process mainly perform binding for objects of sequence tree, checks tables and columns available or not, loading metadata, adding required information for conversations to sequence tree. Replacing views with their definitions.
3. Compilation : Compilation process include extraction of true transact statements(select, insert, update, delete etc) to pass to optimizer, because whole code may include variable declarations, assignments, loops, if conditions etc those not required optimization.
   
4. Optimization : Optimizer first checks whether query's plan would be 'trivial' if it is, it sends this plan for execution. Because trivial plan uses predefined system functions those are optimal.
   If query plan would not be a trivial plan then optimizer collects available statistics for columns, available indexes and prepare simple plan using nested-loop joins. If not enough statistics available then it goes to prepare complex plans with using multiple indexes on one table, if not such indexes available then it goes to use hash joins and evaluates query cost. Then optimizer uses parallelism if system having multi processors & configured to use. Optimizer prepares a number of plans and compare cost of each plan then finalize cost effective plan.
5. Execution : When a plan goes for execution, sql server checks whether a plan already exist in cache. It generates keys for plans to store & identifying later, for stored procedures keys includes procedure name, for T-sql statements it includes information in key based on text of sql statement.
   Recompilation of a cached plan take place based on meta data (columns/ indexes etc) changes, or long time gap between stored and called to execute.
   It also cache the result data.

• Logical Vs Physical operators : 

1. Logical Operators : These operators define concept what need to be performed.
2. Physical Operators : These operators are objects are routines those actually perform operations define by logical operators, ex. one physical operator reads data from column/row of table, other physical operator perform operation on data like calculations, joins, integrity etc. Each physical operator has cost associated with it.
   May more than one physical operators performs operation defined by a logical operator. 

• Logical Vs Physical Reads : 

1. Logical Reads : Number of pages read from data cache
2. Physical Reads : Number of pages read from disk

If we on I/O statistics on below elements shows in output

statistics io on

Output	Description
Table	Name of table scanned
Scan count	0 : If primary key with clustered index present, when searching Primary_key_Column = <<some value>> 1 : when clustered index present but on non-unique column, this count   is to check duplicate values N : when N number of scans started
logical reads	Number of pages read from data cache
physical reads	Number of pages read from disk
read-ahead reads	Number of pages placed into cache for query
lob logical reads	Number of text, ntext, image or large value type pages read from cache
lob physical reads	Number of text, ntext, image or large value type pages read from disk
lob read-ahead reads	Number of text, ntext, image or large value type pages placed into cache

Estimated Vs Actual query execution plan :

1. Estimated Plan : This plan is generated based on statistics available. This plan generated without executing the T-sql. This may have considerable difference compare to actual plan if statistics are out dated.
   - Considering this plan is useful when executing query takes long time
   - Reverting the operations(restoring database in previous state) is difficult after executing query
2. Actual Plan : Actual plan provides accurate figures because it generates by actually executing query.
   Considering this plan is good if you are not in such two conditions mentioned above.