Comparison between a SQL Server Graphical Plan and Text Plan

By: Tibor Nagy | Read Comments (6) | Print Tibor Nagy is a technical and business professional from the financial industry. His experience includes SQL Server 2000-2008, DB2 and MySQL. Related Tips: More

Problem

I have been using Graphical Query Plans for some time, but I know that Text Plans are also available.  I have looked at them a few times, but I don't really understand how to relate what I know about the Graphical Plans to the Text Plan format.  

Solution

In this tip we will take a look at a sample query and how the Graphical Plans maps to the Text Plan output.

Query Plans

There are two different types of query plans, the estimated and the actual execution plan. You can obtain these plans in three formats: text, graphical and XML.  Note that if you try to view an execution plan and have issues you may need to have the SHOWPLAN permission granted to your login. You can read more about SHOWPLAN security on MSDN.

Generally Graphical Plans are easier to read for beginners and also for query plans that are not overly complex.  When the query plan gets more complex Text Plans are generally a better option although they can be overwhelming at first sight. 

Text Plans maybe harder to read, but they contain more information without the need to check the properties for each operation. For Graphical Plans the details for each operation can be displayed using the ToolTips by hovering your mouse over the operation or using the properties window.  When you hover over an operation or click on the operation a list of operator properties is displayed, but unfortunately it can be difficult to check all the details for a complex query using this tool.

Let's check the Graphical and the Text Execution Plans for a query in the AdventureWorksDW2008R2 database and compare the output.

Graphical Plans

You can display the Estimated Execution Plan in SQL Management Studio by pressing CTRL + L in the query window or CTRL + M for the Actual Execution Plan. 

For this example we will use the Estimated Execution Plan and then run the following query.

SELECT * 
FROM dbo.DimOrganization 
LEFT JOIN dbo.DimCurrency ON DimOrganization.CurrencyKey=DimCurrency.CurrencyKey 
WHERE DimOrganization.ParentOrganizationKey IS NOT NULL 
ORDER BY DimOrganization.ParentOrganizationKey,DimOrganization.CurrencyKey

You should read a Graphical Plan from right to left and top to bottom following the arrow between the icons.  I have added red numbers to show the sequence of events below.

On the Graphical Plan you can check that the execution order by following the arrows from right-to-left:

1. Starts with a Clustered Index Scan
2. A Sort and Clustered Index Seek run in parallel
3. Nested Loops
4. Select

The data passed between operations is represented by the thickness of the arrows on the Graphical Plan and if you hover over the arrow you can see the Estimated Number of Rows that are passed between operations. 

Text Plans

There are three ways to obtain text plans:

• SET SHOWPLAN_TEXT: the query is not executed, but returns information about how the statements are executed (Estimated Execution Plan)
  
• SET SHOWPLAN_ALL: the query is not executed, but returns detailed information about how the statements are executed and the resources used (Estimated Execution Plan)
• SET STATISTICS PROFILE: the query is executed and returns detailed information about how the statements are executed, actual number of rows and the resources used (Actual Execution Plan)

To turn these on you use the ON option and turn off you use the OFF option as shown below.

Since we want to get the estimated execution plan with details to match the graphical plan we will run the following code:

SET SHOWPLAN_ALL ON
GO
SELECT * 
FROM dbo.DimOrganization 
LEFT JOIN dbo.DimCurrency ON DimOrganization.CurrencyKey=DimCurrency.CurrencyKey 
WHERE DimOrganization.ParentOrganizationKey IS NOT NULL 
ORDER BY DimOrganization.ParentOrganizationKey,DimOrganization.CurrencyKey 
GO
SET SHOWPLAN_ALL OFF
GO

The Text Plan for the query is below and I broke this up into three screenshots to show all the details.

When you interpret a Text Plan, you should look for the pipes (|) which connect the child and parent nodes.  You can see these in the StmtText column.  Each indentation is another level deeper.  Note that I added red numbers to show the order of operations and these numbers correspond to the numbers above in the Graphical Query Plan.

The Text Plan contains the execution order through the NodeId and Parent columns. The Parent column shows which operation will use the output of the current operator. You should start with a statement where the StmtText starts at the rightmost position behind a pipe character. For this query it is the Clustered Index Scan (NodeId 4).

1. Clustered Index Scan (NodeId 4) runs first
2. Clustered Index Seek (NodeId 5) and Sort (NodeId 3) run in parallel, their parent operator is the Nested Loops (NodeId 2)
3. Nested Loops (NodeId 2) processes the output of the previous operations
4. Select (NodeId 1) is the final operation

On the Text Plans you can easily see all of the detailed properties which are shown in Graphical Query Plan ToolTips.  One such item is the EstimateRows which shows the estimated number of rows passed between operations.

Summary

I hope this helps you see that Text Plans are not that hard to use and as your query plans get more and more complex that this might be a better option to tune your queries.

Next Steps

• Check out these related tips to learn more about Query Plans:
  • How to read SQL Server graphical query execution plans
  • SQL Server Query Execution Plans in SQL Server Management Studio
  • Query Plans category articles

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

USE ADVENTUREWORKS
GO

SET SHOWPLAN_ALL ON
GO
SELECT * FROM Sales.SalesOrderDetail s INNER JOIN Production.Product p ON s.ProductID = p.ProductID 
GO
SELECT * FROM Production.TransactionHistory th INNER JOIN Production.TransactionHistoryArchive tha ON th.Quantity = tha.Quantity 
GO
SET SHOWPLAN_ALL OFF
GO

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code