26 performance tuning questions and solutions, How to tune SQL queries, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, SQL Complex Queries, SQL Interview questions SQL questions SQL SERVER Interview questions SQL SERVER Interview questions Download SQL SERVER Interview questions Free Download SQL SERVER Interview questions SQL SERVER Tips, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL Tuning Overview, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, What is Query Optimizer?
What is Query Optimizer & how optimizer works
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