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?
Understanding Execution Plans?
1. Execution plans tells you how SQL Server may execute your query, or how it did execute a query.
2. It is the primary source of troubleshooting a poorly performing query.
3. Check out the diagram below. Diagram shows all the steps involved in query execution.
4. We can also say that execution plan is the strategy of the optimizer how we can retrieve and manipulate data.
5. We can also influence query optimizer using different techniques. One of the common techniques is query hints.
6. How to read execution plans- Logic flows from left to right and your data flows from right to left. You can check this by checking directions of the arrows.
7. Types of execution plans – Estimated Execution plan and Actual Execution plan.
8. If your query is taking too long time to execution then use estimated execution plan otherwise always use actual execution plan.
9. Estimated execution plan-
a. They are good for long running queries.
b. They are created without running the queries.
c. They use statistics to get execution plan. Means they are based on statistical understanding of data. May not be correct every time. For example multi valued table variable will always give you 1 as the estimated number of rows. In 2014 they have upgraded it to 100 rows.
d. They may produce bad plans if the statistics are not up to date.
10. Actual Execution plan-
a. They are created using real data.
b. They are created when actual query runs.
c. They are not useful if your query is taking too much of time. Although we can run our queries for hours but that will kill out time. So it depends on you which one to choose i.e. actual or estimated.
11. If the actual no of rows and the estimated number are different then we might have to update the statistics. We may have some other issues also like parameter sniffing etc.
12. Other Types of Execution plans- Textual, Graphical and XML.
13. XML execution plans are very good for analysis as they are based out of XML.
14. Graphical execution plan internally used XML execution plan.
15. For reading execution plan we should have ShowPlan permission.
16. In execution plans we have many operators e.g. Index Seek, Clustered Scan, Select etc.
17. Example. Clustered Key lookup.