By: Tibor Nagy | 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.
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
- Check out these related tips to learn more about Query Plans:
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
