What is Query Optimizer & how optimizer works

Download PDF – SQL Query Optimizer

Notes only

1. In SQL SERVER, the query is parsed and then processed by the query optimizer.

2. Query optimizer –> Execution Plan (Output)

3. Optimizer uses statistics. SQL Server generates and maintains against indexes and columns.

4. Optimizer uses query processor tree and statistics then it applies the logic to work out the best way to execute the query – that is, it generates an execution plan.

5. The decisions made by the optimizer are based on what it calculates to be the cost of a given execution plan, in terms of the required CPU processing and I/O. It is called cost-based plan.

6. All in all we can say Query Optimizer is a piece of software. It’s a program. It’s very good software but sometimes can make mistakes.

7. Query plan tells the Query processor  what to do. So we must have a good query plan. Query Plan is based on estimates & statistical understanding of data. Sometimes it may not correlate to real understanding of statistical data.

8. So the bottom line is that Statistics are very important for Optimizer & that’s why we should update statistics regularly as a part of weekly maintenance activity.

9. Please note that Update Statistics will trigger recompilation. Full scan can consume lot of IO and can block index maintenance.

10. Query Optimizer is one of the most important things in Optimization; so we need to think how the optimizer thinks