Export (0) Print
Expand All

Displaying Execution Plans by Using SQL Server Profiler Event Classes

The following SQL Server Profiler event classes capture Showplan information. To display execution plan information by using these event classes, you must also include the appropriate event classes from the Stored Procedures and Transact-SQL Event Categories in your trace definition. For more information, see SQL Server Event Class Reference.

Users must be granted the ALTER TRACE permission to use SQL Server Profiler to display execution plans.

ms190233.note(en-US,SQL.90).gifNote:
The SQL Server Profiler events that capture Showplan information produce one rowset for each query. No Showplan information is generated for encrypted stored procedures or for triggers.

Event class Description

Showplan XML

Occurs when a query executes on SQL Server. It captures the estimated execution plan in XML format with full compile-time details in the TextData data column of the trace. For more information, see Showplan XML Event Class.

Showplan XML For Query Compile

Occurs when a query is compiled or recompiled on SQL Server. This is the compile time counterpart of the Showplan XML event. Showplan XML occurs when a query is executed. Showplan XML For Query Compile occurs when a query is compiled. For more information, see Showplan XML For Query Compile Event Class.

Showplan Text

Occurs when a query executes on SQL Server. It displays the estimated query execution plan tree of the Transact-SQL statement being executed. For more information, see Showplan Text Event Class.

Showplan Text (Unencoded)

Occurs when SQL Server executes a Transact-SQL statement. It displays the same information as the Showplan Text event class, except the event information is formatted as a string rather than as binary data. For more information, see Showplan Text (Unencoded) Event Class.

Showplan All

Occurs when a query executes on SQL Server. It displays the estimated execution plan with compile-time details. For more information, see Showplan All Event Class.

Showplan All For Query Compile

Occurs when a query is compiled or recompiled on SQL Server. This is the compile time counterpart of the Showplan All event. Showplan All occurs when a query is executed. Showplan All For Query Compile occurs when a query is compiled. For more information, see Showplan All for Query Compile Event Class.

Showplan XML Statistics Profile

Occurs during run time. It captures the actual execution plan in XML format with full run-time details in the TextData data column of the trace. For more information, see Showplan XML Statistics Profile Event Class.

Showplan Statistics Profile

Occurs during run time. It displays the actual execution plan with full run-time details in textual format. For more information, see Showplan Statistics Profile Event Class.

Performance statistics

This event is similar to Showplan XML For Query Compile. It occurs when a compiled query plan is cached for the first time, compiled or recompiled any number of times, and when the plan is flushed from the cache. In some cases, the TextData data column for this event contains the plan in XML format that is being compiled or recompiled. For more information, see Performance Statistics Event Class.

In a future version of SQL Server, the following SQL Server Profiler event classes will be deprecated. We recommend that users move to using the newer event classes as soon as possible. The event classes that are scheduled for deprecation are listed in the following table with the new event class that users should use.

Deprecated SQL Server Profiler event Use new SQL Server Profiler event

Showplan All

Showplan XML

Showplan All For Query Compile

Showplan XML For Query Compile

Showplan Statistics Profile

Showplan XML Statistics Profile

Showplan Text

Showplan XML

Showplan Text (Unencoded)

Showplan XML

The following Microsoft SQL Server 2000 SQL Server Profiler event classes that generate Showplan output have been renamed in SQL Server 2005:

SQL Server 2000 event class names SQL Server 2005 event class names

Execution Plan

Showplan Text (Unencoded)

Show Plan All

Showplan All

Show Plan Statistics

Showplan Statistics Profile

Show Plan Text

Showplan Text

ms190233.note(en-US,SQL.90).gifNote:
The trace event IDs remain the same for these event classes.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft