Esat Erkec
Show Execution Plan XML

How to obtain SQL Execution Plans using different methods

June 30, 2022 by

In this article, we will learn various methods of how to get an SQL execution plan of a query.

Introduction

A SQL Execution plan is a very helpful query analysis tool that helps to diagnose the query performance issues and to understand the backside of the complex operation details. A query plan can be likened to an X-ray of a query because;

  • A SQL execution plan offers detailed visualized or XML format reports that describe how a query is performed step by step
  • An execution plan is a very helpful guide when we want to understand which activities are performed or will be performed during a query’s execution. Because the query plan includes all the query execution steps resource consumption, and other statistical measurements
  • An execution plan plays a key role in diagnosing and resolving query performance issues

SQL Server offers two types of execution plans:

  • Estimated Execution Plan displays which steps may do and flow if it executes the query and it only displays the estimated details.
  • The Actual Execution Plan is generated after the successful execution of a query and it includes the actual statistics, unlike the estimated plans.

How to display an SQL execution plan in Azure Data Studio

Azure Data Studio is a new generation database development environment that can be used for on-premises and cloud data platforms. One of the remarkable features of the Azure Data Studio is to work on multiple operating systems:

  • Windows
  • macOS
  • Linux

In order to display the estimated execution plan of a query in Azure Data Studio, we click the Explain button at the top of the query window.

Execution plan in Azure Data Studio

At the same time, we can find out the missing index suggestions under the query text. In the Top Operations tab, we can see a table report that contains various statistics about the estimated query plan operators.

Azure Data Studio Top Operations

The Results tab shows the XML format of the estimated SQL execution plan.

Azure Data Studio estimated XML execution plan

We need to enable the Command Pallete to display the actual query plan in Azure Data Studio. At first, we will expand the View menu and then select Command Pallete.

Actual Execution plan in Azure Data Studio

The Command Palette is a searchable command prompt that can be used to practically run commands or change settings. We type the “actual” keyword in the box and click the Run Current Query with Actual Plan.

Run Current Query with Actual Plan option in Azure Data Studio

In this way, we will both execute the query and also we will obtain the actual query plan.

Graphical query plan

Tip: With the help of the extensions we can gain new functionalities to Azure Data Studio. For example Queryplan.show extension visualizes the query plan in a different style.

Graphical query plan

How to display an SQL execution plan in SQL Server Management Studio

SQL Server Management Studio (SSMS) is an advanced development environment that enables us to configure, manage and administrate SQL Server databases. At the same time, SSMS allows us to develop SQL queries. SSMS includes two simplistic features that help to obtain estimated and actual query plans. Under the Query menu of the SSMS, we can find out the following options:

  • Display Estimated Execution Plan
  • Include Actual Execution Plan

SSMS execution plan

As an alternative method, we can use two small buttons where are located on the SQL editor toolbar.

SSMS toolbar

After clicking any of these buttons the graphical execution plan will appear on the Execution Plan tab. When we hover the mouse icon over the query plan operators, a detailed pop-up will be shown that includes detailed info about the operator.

Query plan operator details

The plan arrows point out the data directions between the operators and how many rows are pumped into the next operator.

Query plan arrow details

When we want to show the execution plan in an XML format, we right-click on the execution plan and choose the Show Execution Plan XML menu item.

Show Execution Plan XML

After clicking this menu item, the plan will be displayed in XML format in a new window.

SSMS XML plan

Using the SHOWPLAN command

Besides the graphical query plans, we can obtain the execution plans in text or XML format with help of the SET SHOWPLAN command. The first command that we will learn is SHOWPLAN_TEXT. When we enable the SHOWPLAN_TEXT option before a query, a hierarchical tree result set will return that includes which operators will be performed in order. In fact, this result set contains very limited information, so it would not be very realistic to consider it as a complete query plan.

SHOWPLAN_TEXT command

The SHOWPLAN_ALL is another option that we can use to get an estimated execution plan in a text format. The returned result set includes the estimated row numbers and other resource cost estimations.

SHOWPLAN_ALL command

With help of the SHOWPLAN_XML command, we can display the estimated execution plan in an XML format. This command can be considered the same as XML formatted of the estimated query plan.

SHOWPLAN_XML command

As a last, we will take a glance at the STATISTICS PROFILE. After enabling this command, we can obtain the actual execution plan in text format with the query result set.

STATISTICS PROFILE command

Using SQL Server query plan cache

SQL Server stores the compiled plans in the query plan cache so they can be reused by the query optimizer for the next execution of the queries. To view these cached plans we can use the dm_exec_cached_plans dynamic management view. Through the following query, we can return cached query plans.

Get SQL execution plan to using query plan cache

UseCounts: How many times the cached query plan is referenced.

Cacheobjtype: Type of object in the cache.

Objtype: SQL Server can store query plans for different objects and this column shows the database object type.

TEXT: Cached object literal query text.

query_plan: Estimated SQL execution plan.

Using Query Store

SQL Server Query Store stores the SQL execution plan and performance metrics of the queries automatically. The main advantage of the Query Store is to offer different reports to analyze the query performance and enable force a query to use a specific execution plan. The sys.query_store_plan internal table stores details about the SQL execution plans that are related to the queries that are captured by the query store.

Get query plan to using query store

The displayed execution plans are the estimated SQL execution plans.

Using Extended Events

An Extended Event is a monitoring tool that captures the various activities that are performed in the database engine. The events can capture estimated or actual query plans of the executed queries:

query_post_compilation_showplans: This event captures the estimated query plan of a query when it is initially compiled.

query_post_execution_showplan: This event captures the actual SQL execution plan of a query when it is initially compiled.

The following query will create an extended event that includes the above-mentioned events. Additionally, it will filter the captured event only for the particular database.

After creating and enabling the extended event, we will launch the Watch Live Data screen to observe the captured events. Firstly, we will clear the query plan cache for the Adventureworks database.

As a next step, we will execute our sample query two times and interpret the captured data that are

Watch Live Data screen In the first execution of the query, the query plan is not in the plan cache therefore the query is initially compiled, and then it is added into the query plan cache. For this reason, the query_post_compilation_showplan event is captured but in the second execution, we can not see this event because the optimizer finds an appropriate cached plan and use it. We can obtain the graphical execution plan by jumping the Query Plan tab.

SQL extended event details

Summary

SQL execution plans include all details about query execution steps therefore it plays a crucial role to understand the back-stage activities and resolve the performance issues. In this context, we can use different tools or commands to display query plans:

  • Azure Data Studio
  • SQL Server Management Studio
  • SHOWPLAN command
  • Query Store
  • Extended Events
Esat Erkec
168 Views