Reading Query Plans with the Showplan Options

This post is about the several ways of reading the query plan (called the showplan options). Now that we know what a query plan is, why it’s helpful and what it consists of (iterators), let’s find out how we can read the query plans in SQL Server.
I believe most of you are familiar with enabling the Actual (or Estimated) Execution Plan from the SQL Server Management Studio’s toolbar or by right-clicking in the query window and selecting from there.
But there are some other ways to read a plan. These ways are known as “the showplan options”.

Before we dive right into these methods, I want to clarify something: there are two types of plans:

  1. the actual execution plan
  2. the estimated execution plan

The actual execution plan is the plan that is actually used at query execution. It shows the real operators, with their costs and results. This plan is the one we (mostly) use when doing performance troubleshooting.
On the other hand, for the estimated execution plan, SQL Server creates a plan without actually executing the query. So it will only guess what is going to happen at query execution. This also means that the query will not return any results, only the plan.
We should not take this plan for granted, because there are cases when the actual execution plan, the real one, will be totally different than what we see in the estimated plan.
Why? For example, the server might choose to recompile the query and generate a new plan.
While we should not rely on it in all situations, the estimated plan is useful in several scenarios:

  • Maybe we have a query that takes a long time to complete and we cannot wait until the actual plan is generated;
  • Or maybe we would like to look at the plan for DML statements: inserts, updates, deletes, without actually making the changes in the database

 The Showplan Options

We have the following options for looking at an execution plan:

  1. in a graphical manner
  2. in text mode
  3. and as an XML.

All these options show the same plan, the only differences being:

  • How the information is formatted
  • The level of detail that is included
  • How we can read it
  • And how we can use it afterwards

The following image shows the various showplan options and at whether they are used for generating the actual execution plan or the estimated one.

showplan options


We can see that the text options (SET SHOWPLAN_TEXT ON and SET SHOWPLAN_ALL ON) are only for estimated plans. This means that the query is not being executed when these options are used.

These are deprecated commands, so try not to use them a lot, because they will be removed in future versions of SQL Server. You can read here more about the methods that are deprecated.

PS: They still exist in SQL Server 2016.

Another option for the estimated plan is SET SHOWPLAN_XML ON.

For viewing the actual execution plan (which means that you see the real plan used in the query execution process), you have the following options:

  • SET STATISTICS PROFILE ON (but keep in mind that this one is also deprecated)
  • SET STATISTICS PROFILE XML, which is a still current option
  • And you have, of course, the graphical manner.

For showing you the different options to visualize a plan, I will use the HappyScoopers database. You can download it from here.

Let’s start with the commands for estimated plans.

SET SHOWPLAN_TEXT ON

The first command is SET SHOWPLAN_TEXT ON.

I will execute it and then I’m going to execute my query.

What you can see here is that the query doesn’t return any rows, only some text. This is because it’s used to show an estimated plan and when we use these options, the query is not being executed.

But let’s see what this option shows us.

showplan options - set showplan_text

In the first column we have the text of the query. So if I copy it from here and paste it in another location, I will see my exact query.

In the second column we can see a tree-like structure. This is actually the query tree and on each line there is a different iterator present in the plan. You notice the tree structure because at the beginning of each rows there are vertical bars with spaces for indentation. You can also notice that there are no arrows between iterators. We need to remember that data is propagated bottom up, from a child to a parent in the tree.

In order to get to the second demo, I need to disable the SHOWPLAN_TEXT option. So I will execute the command: SET SHOWPLAN_TEXT OFF and move on.

 

SET SHOWPLAN_ALL ON

The second demo is for another estimated plan command: SHOWPLAN_ALL. This one contains more output than the first command.

We can see the execution tree in the first column, but then there are other columns as well:

  • The statement ID, for the case when the query contains 1 or more statements executed as a whole
  • The nodeId in the tree
  • The physical and logical operation implemented by the iterator. While at a first look these may seem the same, they are not and we will discuss about this later in this tutorial. For example, you can take a look at these columns: the logical operation is an inner join, which we all know and love, but the physical implementation differs: for one it translated to a Nested Loops join and for another to a Hash Match. If you are eager to learn why this is happening and what does each of them mean, please be patient and keep watching these clips.
  • There are also columns with estimations: estimated number of rows, IO and CPU cost, Average row size and so on. These are not actual values and sometimes they can be completely off, but most of the time they provide a pretty accurate estimation.

showplan options - set showplan_all

Moving on to the next demo, I first disable the previous one:

 

SET SHOWPLAN_XML ON

And let’s see what the SET SHOWPLAN_XML gives us.

The output looks like this:

showplan options - set showplan_xml

I see a link and if I click on it, a new tab opens with the graphical execution plan. The XML plan and the graphical plan are interchangeable, so if I look at a graphical plan, I have access to the XML behind it and also the other way around.

showplan options - set showplan_xml

In the XML plan, the nesting of elements is more clear than in the other options we’ve revised.

We can make use of the XML functionality to expand or minimize some nodes, to better read the text.

Another advantage is that this type of format can be easily integrated with other applications. It is quite easy to read from an XML if you are a developer.

The nicest part is that we also have the possibility of viewing the plan in a graphical manner, even if we only mentioned that we want to see the XML plan. We can see in this format cost estimates and statistics and also specific information for all iterators in the plan.

So this is a nice option, but in my opinion, it’s a little hard to read when you’re troubleshooting performance.

For the next test, I disable this option.

SET STATISTICS PROFILE ON

Now we are going to look at a showplan option that retrieves the actual execution plan. This means that the query is executed and it will return the real plan used for its execution. 

I am going to use SET STATISTICS PROFILE ON now.

Below are the results of the query. So what do we see? 

showplan options - set statistics profile

In the first part, we get the actual results of the query. In the second part, the information is similar to what we got when using the SHOWPLAN_ALL option. However, right now we have more information than in the SHOWPLAN_ALL: with this option, we see the actual number of rows returned and the actual number of executions per iterator. These are the first two columns in the output table. We can see this information because the query was actually executed this time.

It is useful to have the estimates and the actuals in the same place, because this way, we can compare the values. And if we find differences (for example, the actual number of rows returned by my query is 29, while the estimated number is 25.9), we can start investigating cardinality estimates issues. But this is something we will discuss a little bit later.

SET STATISTICS XML ON

I now disable the STATISTICS PROFILE option in order to get to the last test: the one for SET STATISTICS XML ON.

Now, for testing the last showplan option from this post, I will execute the following query and look at the results.

This showplan option is also for the actual execution of the query, so the query results will be returned. On a different tab, we see the query plan in XML format.

showplan options - set statistics xml

If I click on the XML link, it will open the graphical plan. The difference is that this is the actual execution plan, so now I see information about the actual resources that were consumed for this query. If I look at the tool tip for this Nested Loops join, I see the Estimations, together with the Actual numbers of rows and batches.

showplan options - set statistics xml on - graphical plan

If I want to see the XML format, I right click in the query window and select “Show Execution Plan XML”.

showplan options - set statistics xml - right click

And with this, I conclude my demos for the showplan options.

Besides these options there is,  of course, the possibility to view the plan in the graphical manner. If you don’t know what I’m talking about, check out my next post to find out how to enable this option, how to use it and other interesting things.

I am curious what is your favourite way of reading a query plan, or the one you most commonly use when troubleshooting performance. I expect your opinions in the comments below.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

sixteen − 13 =