Understanding Execution Plans?

Download PDF Version – SQL Server Execution Plans ?

Notes only

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.