Viewing Query Plans

In my last post, I explained that SQL Server executes a query by compiling it into an iterator tree which we call a query plan.  So, if we want to understand iterators and query execution better, we need a way to look at query plans.  Fortunately, SQL Server makes it easy to do exactly that with showplan.

How many different types of showplan are there anyhow?

In SQL Server 2000, there are two types of showplan: text and graphical.  In SQL Server 2005, we also have XML showplan.  Each of these different output formats is logically equivalent; they just have different strengths.

Graphical showplan uses visually appealing icons that correspond to the iterators in the query plan.  The tree structure of the query plan is clear.  We use arrows to represent the data flow between the iterators.  “Tool tips” provide detailed help including a description of and statistical data on each iterator.  The statistical data includes estimates of the number of rows generated by each operator (i.e., the cardinality estimates), the average row size, and the cost of the operator.  Generally, graphical showplan gives a quick view of the “big picture” though sometimes for very large query plans the picture is so big that it can only be viewed by scrolling in two dimensions.

Text showplan represents each iterator on a separate line.  We use indentation and vertical bars (the “|” character) to show the child-parent relationship between the iterators in the query tree.  There are no explicit arrows but data always flows up the plan from a child to a parent.  Once you understand how to read it, text showplan is often easier to read – especially when big plans are involved.  Text showplan is also easier than graphical showplan to save, manipulate, search, and/or compare.

XML showplan combines many of the best advantages of graphical and text showplan.  XML is much better suited than text to represent the tree structure of a query plan.  Although the native XML format is more challenging than to read directly compared with text showplan, it is simple to save and process or query using any standard XML tools including the XML capabilities built into SQL Server 2005.  In addition, SQL Server Management Studio can save graphical showplan as XML (with a .sqlplan extension) and can display saved XML showplan in a graphical format.  (Actually, in SQL Server 2005, graphical showplan is really just a GUI for XML showplan.)

Do I need to run a query to get the plan?

No.  You can ask SQL Server for showplan output with or without running a query.

We refer to a query plan generated without executing a query as the “estimated execution plan” as SQL Server may choose to recompile the query (recompiles may occur for a variety of reasons) and may generate a different query plan at execution time.  The estimated execution plan is useful for a variety of purposes such as viewing the query plan of a long running query without waiting for it to complete or viewing the query plan for an insert, update, or delete statement without altering the state of the database or acquiring any locks.  The estimated execution plan does include the cardinality estimates, row size, and cost estimates mentioned above.

We refer to a query plan generated after executing a query as the “actual execution plan.”  The actual execution plan includes the same information as the estimated execution plan plus actual row counts.  This output can be very useful for comparing the estimated and actual row counts.

How do I get showplan output?

You can get showplan output directly from Management Studio or Query Analyzer, through SQL Profiler (the showplan events are grouped with the “performance” event class), or by querying the sys.dm_exec_query_plan DMV.

For instance, to get graphical showplan in Management Studio without running a query, choose the “Display Estimated Execution Plan” function from the Query menu or the SQL Editor toolbar or press Ctrl+L.  To execute a query and get graphical showplan including actual row counts, choose the “Include Actual Execution Plan” function or press Ctrl+M before executing the query.

To get text or XML showplan in Management Studio, execute one of the following statements.  These statements must be the only statement in the batch.

	Command	Execute Query?	Display Plan Text?	Display Estimated Row Counts & Stats	Display ActualRow Counts
Text	set showplan_text on	No	Yes	No	No
	set showplan_all on	No	Yes	Yes	No
	set statistics profile on	Yes	Yes	Yes	Yes
XML	set showplan_xml on	No	Yes	Yes	No
	set statistics xml on	Yes	Yes	Yes	Yes

Note that you can ask for text showplan with or without cardinality estimates and other statistical data.  The more concise showplan_text can be useful when you do not need the full detail of showplan_all.

Back to that simple example

Recall the simple example and query plan from my last post:

select

count(*) from t

<-- count(*) <-- Scan [t]

Recall also that we implement the scan using the “table scan” operator and the count(*) using the “stream aggregate” operator.

At this point, the corresponding graphical showplan should look reasonably familiar (albeit with nice icons):

Control (i.e., calls to Open, GetRow, and Close) flows from left to right while data flows with the arrows from right to left.  (Ignore the compute scalar; this is just performing a minor type conversion.  The SELECT icon on the left at the root of the tree is just informing us that this is a query plan for a select statement; it is not actually an iterator.)

The text showplan appears as follows:

select count(*) from t
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|--Table Scan(OBJECT:([tempdb].[dbo].[t]))

Note that we have the same three operators, but now they are placed one per line and control flows down instead of to the right while data flows up the tree rather than to the left.  Moreover, we can immediately see that the stream aggregate performs a count(*); in the graphical showplan we have check the properties window to see the same thing.

The XML showplan is a bit too verbose to include here in its entirety.  However, the schema is available here.  Start by concentrating on the top level tags such as <QueryPlan> (hopefully self-explanatory) and <RelOp> (short for relational operator) which corresponds to a single iterator.

Now for a slightly more complex example

Finally, I’d like to present an example of a query plan with an operator that has two children.  This example should make the tree structure of a query plan more apparent.

Consider the following query:

select

a from t1 union all select a from t2

This query combines the output from two tables.  To achieve this result, we need two table scan operators (one for t1 and another for t2).  (Remember that all query plans are built out of the same set of basic operators.)  To combine the results from two different sub-trees (in this case two different table scans), we use the “concatenation” operator.  The concatenation operator can have more than one child.  It reads and returns all rows from its first child and then proceeds to do the same with its next child.

The graphical showplan is:

Note that when an operator has more than one child, the order of the children matters.  The topmost child is the first child while the bottommost child is the second.  The concatenation operator processes the children in this order.

The text showplan is:

select a from t1 union all select a from t2
|--Concatenation
|--Table Scan(OBJECT:([tempdb].[dbo].[t1]))
|--Table Scan(OBJECT:([tempdb].[dbo].[t2]))

Notice that the two children of the concatenate operator are indented to the same depth.  By following the vertical bars (the “|” characters), we can see the relationship between parent and child operators.  Again, the topmost table scan is the first child while the bottommost table scan is the second.

That’s it for now

There’s a lot more that you can do with showplan.  I’ll touch on some of the other information such as statistics and row counts on another day.

Comments

• Anonymous
  November 28, 2006
  The comment has been removed

• Anonymous
  December 20, 2011
  Nice article

• Anonymous
  August 25, 2014
  Is there a way to create a proc and return the estimaterow count?  I need to return that number back to the front end.

• Anonymous
  August 27, 2014
  The comment has been removed