Cost based Optimization 

How does Sql do that. It selects the best plan based on the cost

In real time scanrio like travaelling, we had data ncluded both the amount of time and the number of kilometres travelled. on basis of that we made our route selection based on these two statistical variables. SQL Server, too, utilises such statistical figures to determine the cost.

 

Sql gathers Statistics like

• the no of columns
• what are the data types of columns 
• How many row sets are there and what is the length of each row sets 
• What is bytes and how much percentage on that draw
• what is the block size (DB_BLOCK_SIZE)
• Statistics, Last Analysis date
• Percentage of distinct value per column
• Indexex available on table
• All Constaints on that table

 

This way SQL is gathering all the necessary information for it  and it stores in a plan table so that it can select the best path.

Without statistics, Sql cannot make a decision. So Sql method of optimization is called cost based optimizations