Different Types of Query Plans


By:

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


Last Update: 8/15/2011




Comments For This Article





download














get free sql tips
agree to terms