Differences Between SQL Server Query Plan Formats

By: Tibor Nagy   |   Updated: 2011-06-28   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Query Plans

Problem

I am familiar with the basic database maintenance tasks on Microsoft SQL Server, but now I received a new assignment. I have to optimize a few queries and I know that I have to check the query plans. Can you please explain which type of query plan I should use? What are the differences between text based and graphical query plans? Check out this tip for all of the details.

Solution

In general, there are two different types of query plans, the estimated and actual execution plan. You can obtain them in three different formats: text, graphical and XML.
Please note that you will need to be granted the SHOWPLAN permission to create the below discussed execution plans. You can read more about SHOWPLAN security on MSDN.

Text Based SQL Server Query Plans

There are three ways to obtain text plans:

• SET SHOWPLAN_TEXT: SQL Server does not execute the query, but returns information about how the statements are executed. It shows information about the Estimated Execution plan.
  
• SET SHOWPLAN_ALL: SQL Server does not execute the query, but returns detailed information about how the statements are executed and the resources used for the query execution. It shows information about the Estimated Execution plan.
• SET STATISTICS PROFILE: SQL Server executes the query and returns detailed information about how the statements are executed, actual number of rows processed and the resources used for the query execution. Shows information about the Actual Execution plan.

Please note that Microsoft issued a depreciation announcement for the above SET SHOWPLAN options and they recommend using the XML plans instead.

SQL Server Graphical Query Plans

You can display the Estimated Execution Plan in SQL Server Management Studio by pressing CTR + L in the query window. You can include the Actual Execution Plan in the results set by pressing CTR + M. Check out these additional SQL Server Management Studio shortcuts.

SQL Server XML Based Query Plans

XML plans provide the most complete set of information in the highly portable XML format. There are two ways to obtain XML plans:

• SET SHOWPLAN_XML: SQL Server does not execute the query, but returns detailed information about how the statements are executed and the resources used for the query execution. Returns a well-formed XML document containing the Estimated Execution plan.
• SET STATISTICS XML: SQL Server executes the query and returns detailed information about how the statements are executed, actual number of rows processed and the resources used for the query execution. Returns a well-formed XML document containing the Actual Execution plan.

Differences Between Text and Graphical Based Query Plans

Generally, the Graphical Query Plans are the easiest to read, so beginners should start with them. You should read a Graphical Plan from right to left and top to bottom following the arrow between the icons. Also the Zoom In and Zoom Out functions are very handy and useful.

The details of the Graphical plans can be displayed using the ToolTips. You just simply scroll to an icon and point your mouse on it. A list of operator properties will be displayed as shown in the screenshot below. Unfortunately it can be difficult to check all the details of a complex query using this tool.

The Graphical Plans can be saved in XML format and this format is the connection between the traditional Text and the new XML format.

Text Plans are not for beginners, they are harder to read. They do not have such easy to follow rules for interpreting them. You start somewhere in the middle and look for the pipes (|) which connect the child and parent nodes. The result set contains every detail so you do not have to use the ToolTip. Actually, it also includes an additional column with the Parent Node ID.

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

About the author

Tibor Nagy is a SQL Server professional in the financial industry with experience in SQL 2000-2012, DB2 and MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips