By: Tibor Nagy
Overview
Before we get into how to read the plans, I wanted to note that in addition to Graphical Plans there are some alternatives such as text based and XML plans. These are the different ways SQL Server can display a query plan.
Explanation
Text Plans
There are three ways to obtain text plans:
- SET SHOWPLAN_TEXT: the 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: the 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: the 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. This 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 going forward.
Graphical Plans
There are two options for graphical plans:
- You can display the Estimated Execution Plan in SQL Management Studio by pressing CTRL + L in the query window.
- You can include the Actual Execution Plan in the results set by pressing CTRL + M.
XML Plans
XML plans provide the most complete information in a 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.
Additional Information
- Differences Between SQL Server Query Plan Formats article on MSSQLTips
- Displaying Execution Plans by Using the Showplan SET Options article on MSDN
