showplan

This statement is similar to SET SHOWPLAN_TEXT, except that the output is in a format more verbose than that of SHOWPLAN_TEXT.

Graphical display of Showplan information showing the logical steps built into the execution plan of a Transact-SQL statement.

Users must also be granted the SHOWPLAN permission for all databases containing referenced objects .

Neither of these SET statement options check for or require the SHOWPLAN permission.

Gather Streams logical operators as subtypes , one or more of which can appear in the Showplan output of a query plan for a parallel query .

SHOWPLAN permission on all databases containing objects that are referenced by the Transact-SQL statements.

Attribute, the XML Showplan produced will still be correct , but it may not be optimal .

This is usually the root element of a showplan.

Identifies the Showplan operators associated with a SQL statement.

SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements.

When the Showplan Statistics Profile event class is included in a trace, the BinaryData data column must be selected.

SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified in a stored procedure.

No Showplan output is generated for the entire batch if any statement execution or permission check errors are encountered.

SET SHOWPLAN_ALL returns more detailed output intended to be used with programs designed to handle its output.

When the major version number increments, new XML Showplan output may not validate against the old schema.

Node IDs in Showplans identify each operation the query optimizer performs when it generates a query execution plan.

Showplan execution plan information can be produced by various ways.

The additional result set contains the SHOWPLAN_ALL columns for the query and these additional columns.

The Showplan SET options display no information about encrypted stored procedures or triggers.

These operations are called operators and each operator in a Showplan has a Node ID.

SET SHOWPLAN_XML ON allows an XML showplan to be delivered as a single-value result set.

Identifies Showplan operators within a SQL statement.

Any SET statement can be specified inside a stored procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL.

No Showplan information is generated for encrypted stored procedures or for triggers.

SET SHOWPLAN_ALL is intended to be used by applications written to handle its output.

Estimated cost of the Showplan text.

SET STATISTICS XML also produces showplan output, but in XML format, and executes the query.

The following example shows how SHOWPLAN permissions are checked for dynamic SQL statements.

The Showplan returned contains correct results .

The Bitmap Create operator appears in the showplan output where bitmaps are built.

No SHOWPLAN permission is checked on the USE master statement.

No SHOWPLAN permission is checked for the EXEC statement because no Showplan output is generated by it.

The SHOWPLAN permission is not required.

Displays compile time data for Showplan operators.

Perform query analysis by saving Showplan results.

Displays compile time data for Showplan operators in XML format.

Showplan All occurs when a query is executed.

Event is occurring when the query optimizer generates an execution plan, you should also collect a Showplan event class in the trace.

The following example shows how to retrieve the XML Showplan for a slow-running query or batch.

The following example shows how to retrieve the Showplan for a slow-running query or batch.

When Is the SHOWPLAN Permission Checked?

Include the Showplan XML event class to identify the Showplan operators.

Corresponds with the Node ID in Showplans.

When SET SHOWPLAN_TEXT is OFF, SQL Server executes statements without generating a report with execution plan information.

This is the compile time counterpart of the Showplan All event.

SET STATISTICS PROFILE produces showplan information in textual form, but also executes the query.

The SHOWPLAN permission is not checked for queries that reference previous versions of SQL Server.

Have SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements, such as tables, views, and so on.

When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.

In Microsoft SQL Server 2005, Showplan execution plan output can be generated in XML format by various methods.

The SHOWPLAN permission is not checked nor is it required.

The Showplan XML For Query Compile event class occurs when Microsoft SQL Server compiles an SQL statement.

Showplan XML stores a query plan that is created when the query is optimized.

The Showplan Text event class occurs when Microsoft SQL Server executes an SQL statement.

When SET SHOWPLAN_TEXT is ON, SQL Server returns execution information for each Transact-SQL statement without executing it.

Setting the compatibility level to 80 does not produce the Showplan permissions behavior of Microsoft SQL Server 2000.

In a future version of SQL Server, the following Showplan SET options will be deprecated.

For example, "Version 2. 5. " This version number appears in the Showplan XML Schema document root element.

Added note recommending that Showplan event classes also be collected to determine which query tuning operators are emitting the

Database Engine Tuning Advisor submits Showplan requests as part of the tuning process.

The XML Showplan output for each plan for which the server has gathered statistics currently in the plan cache is in the

CASCADE (because SHOWPLAN cannot be granted using the WITH GRANT OPTION clause)

Syntax for Granting, Denying, and Revoking the SHOWPLAN Permission

Under the following conditions, no Showplan output is returned in the plan column of the returned table for sys. Dm_exec_text_query_plan

To avoid granting the SHOWPLAN permission to each user specified in the LoginName column of the trace

Cursor Logical and Physical Showplan Operators

Permissions Required to Use Showplan SET Options

Considerations for Using the Showplan SET Statement Options

Saving XML Showplan Output to a File

For more information about the Showplan event classes, see

Version designation of the Showplan XML Schema consists of two parts, such as

Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)

For more information about the Showplan SET statement options, see the following topics

Using the output from the XML Showplan MissingIndexes element

Analyzing Queries with SHOWPLAN Results in SQL Server Profiler

How to: Save Showplan XML Statistics Profile Events Separately (SQL Server Profiler)

Name Changes for Showplan Event Classes in SQL Server 2005