mssqltips logo

Differences Between SQL Server Query Plan Formats

By:   |   Updated: 2011-06-28   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > 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.
text based sql server query plans

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 graphical query plans

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.
sql server xml based query plans

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.


differences between text and graphical based query plans

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.

Text Plans are useful when you have some experience with the execution plans and know what to look for, but it can be overwhelming at the first sight.

Next Steps


Last Updated: 2011-06-28


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools