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:

  • Choose the Display Estimated Execution Plan option from the Query menu, as shown below:

  • Click on the Display Estimated Execution Plan icon from the taskbar, as follows:

  • Right-click on the query editor and choose the Display Estimated Execution Plan option, as shown below:

  • Type the Ctrl+L keyboard shortcut, and the Estimated Execution Plan for the submitted query will be displayed without the need to execute it, as in the snapshot below:

    The Actual Execution Plan, unlike the Estimated Execution Plan, cannot be displayed directly. It requires the submitted query to be executed in order to be displayed. The Actual Execution Plan can be included in the query using different ways:

    • Choose the Include Actual Execution Plan option from the Query menu, as shown below:

    • Click on the Include Actual Execution Plan icon from the taskbar, as follows:

    • Right-click on the query editor and choose the Include Actual Execution Plan option, as shown below:

    • Type the Ctrl+M keyboard shortcut, and the Actual Execution Plan for the submitted query will be Included. In other words, the Actual Execution Plan option will be turned ON for that query, or any query executed in that window. When you execute the query, the Actual Execution Plan will be displayed, as in the snapshot below:

    For more information about the Actual and Estimated Execution Plans, check SQL Server Estimated Vs Actual Execution Plans.

    In SQL Server 2016, Microsoft introduced a new feature that provides us with the ability to monitor run-time statistics, also called the Live Execution Statistics, for each operator in the execution plan and track the flow of the data between the different operators during the query execution process. The execution statistics include the number of processed rows, the time consumed by each operator and the overall progress of the query execution.

    Live Query Statistics can be included in different ways:

    • Choose the Include Live Query Statistics option from the Query menu, as shown below:

    • Click on the Include Live Query Statistics icon from the taskbar, as follows:

    • Right-click on the query editor and choose the Include Live Query Statistics option, as shown below:

    • Open the Activity Monitor window and right-click on the target query from the Active Expensive Queries section then choose the Show Live Execution Plan option as shown below:

      After including the Live Query Statistics, run the query and monitor the run-time execution statistics of the query and how the data flows between the operators, as shown below:

    For more information about the new Live Query Statistics feature, see Troubleshoot SQL query performance using SQL Server 2016 Live Execution Statistics.

    Execution Plans Formats

    In SQL Server, the Execution Plan that is generated for each query can be viewed in three different ways, depending on the level of detail you manage to view, and the plan generation method. The three Execution Plan formats are:

    • Graphical Format:

      The graphical view of the SQL Server Execution Plan is the most common and friendly plan format, that provides us with a quick and easy way to read the plan and analyze it. The graphical views include the Actual, Estimated and Live Query Statistics plans discussed earlier in this article. The only dark point of the graphical format of the execution plan is that the execution details are hidden behind the plan arrows and operators’ properties, as shown below:

    • Text Format:

      The text plan provides us immediately with the least details about the query execution, in a flat text view, making it quite difficult to analyze and not preferred by the database administrators.

      The text plan comes in three formats:

      • SHOWPLAN_ALL: Displays the Estimated Plan of the query with complete set of information about the query execution, as shown below:

      • SHOWPLAN_TEXT: Displays the Estimated Plan of the query with very limited set of information about the query execution, as shown below:

      • STATISTICS PROFILE: Displays the Actual Execution Plan of the query with complete set of details, as shown below (see how the query results are shown):

    • XML Format:

      The XML plan provides us with a complete set of information about the query execution viewed in XML structure, that is difficult to be read and analyzed and easy to be shared with other experts for troubleshooting purposes.

      The XML Execution Plan comes in two formats:

      • SHOWPLAN_XML: Displays the Estimated Execution Plan of the query, as shown below (Recall that you can easily reach the XML plan from the graphical plan using the Show Execution Plan XML option):

      • STATISTICS XML: Displays the Actual Execution Plan of the query, as shown below:

    Till this point, we became familiar with the different types and formats of the SQL Server Execution Plans, the differences between these types and formats and how we can take benefits from these types. Stay tuned for the next article, in which we will discuss the different components of the Execution Plan and how to analyze it.

    Table of contents

    SQL Server Execution Plans Overview
    SQL Server Execution Plans Types
    How to Analyze SQL Server Graphical Query Plan Components
    SQL Server Execution Plans Operators – Part 1
    SQL Server Execution Plans Operators – Part 2
    SQL Server Execution Plans Operators – Part 3
    < SQL Server Execution Plans Operators – Part 4
    Journey with SQL Server Execution Plan 3rd Party Tool – ApexSQL Plan
    SQL Server Execution Plans and Queries Performance Tuning
    Saving the SQL Server Execution Plans

    References


    See more

    To view and analyze SQL Server query execution plans for free, check out ApexSQL Plan

    Ahmad Yaseen

    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
    Ahmad Yaseen

    Latest posts by Ahmad Yaseen (see all)

Execution plans

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

542 Views