By: Tibor Nagy | Updated: 2012-01-10 | Comments (7) | Related: More > Query Plans
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.
In this tip we will take a look at a sample query and how the Graphical Plans maps to the Text Plan output.
SQL Server 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.
SQL Server 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:
- Starts with a Clustered Index Scan
- A Sort and Clustered Index Seek run in parallel
- Nested Loops
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.
SQL Server 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).
- Clustered Index Scan (NodeId 4) runs first
- Clustered Index Seek (NodeId 5) and Sort (NodeId 3) run in parallel, their parent operator is the Nested Loops (NodeId 2)
- Nested Loops (NodeId 2) processes the output of the previous operations
- 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.
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.
- Check out these related tips to learn more about Query Plans:
Last Updated: 2012-01-10
About the author
View all my tips