Ahmad Yaseen

SQL Server Execution Plans types

July 23, 2018 by

In the previous article, we described, in detail, the different stages that a submitted SQL Server query goes through and how it processed by the SQL Server Relational Engine. The SQL Server Relational Engine generates the Execution Plan and the SQL Server Storage Engine performs the requested data retrieval or modification process. In this article, we will discuss the different types and formats for SQL Server Execution Plans.

Execution Plans types

The SQL Server Execution Plan is the graphical representation of the execution roadmap of the submitted query that will be followed by the SQL Server Query Optimizer, with the cost of executing every part of the query code. SQL Server provides us with two main types of execution plans.

  • The first type is the Estimated Execution Plan. It is the plan that is generated by parsing the submitted query as an estimate of how the query will be executed, without being executed.
  • The second type is the Actual Execution Plan, that is generated by executing the submitted query, displaying the actual steps that followed while executing the query.

The Actual Execution Plans are preferred most of the time by database administrators as it shows more information about query execution statistics. You can benefit from the Estimated Execution Plan in troubleshooting complex queries that take a long time to run, where you will be able to generate the execution plan without the need to execute it. But in some cases, the Estimated and the Actual Execution Plans can differ greatly, due to the difference between actual table data and statistics. Frequent data insertion and modification operations can also make the statistics less accurate.

This issue can be fixed by updating the table’s or index’s statistics. In some situations, the Estimated Execution Plan will not be able to be generated when the query has a temporary table that has not been created yet, as the query is not executed to create the table, causing an error message like the below:

The Estimated Execution Plan can be viewed in different ways:

Execution plans, Query analysis

About Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience. He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs View all posts by Ahmad Yaseen

1,144 Views