SQL SERVER DBA, Linux and Azure: EXECUTION PLAN

EXECUTION PLAN

 What is execution plan and explain it? 

Execution plan graphically displays the data retrieval methods chosen by SQL Server. It represents the execution cost of specific statements and quires in SQL Server. This graphical approach is very useful for understanding the performance of the query. 

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by SQL Server Query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure science the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the query drop-down menu). If this option is tuned on it will display query execution plan in separate window query is ran again.

Execution plan displays:

1. Physical operations

2. Logical operations

3. Actual Number rows

4. Estimated I/O cost

5. Estimated CPU cost

6. Number of Executions

7. Estimated Number of Executions

8. Estimated Operator cost

9. Estimated Subtree cost

10. Estimated Number of Rows

11. Estimated Row Size

12. Actual rebinds

13. Actual rewinds

14. Key lookup

15. Nested look up

16. Index seek

17. Index scan

No comments:

Post a Comment