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.
Note
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. |
Event Classes Scheduled for Deprecation in Future Showplan Versions
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 |
Name Changes for Showplan Event Classes in SQL Server 2005
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 |
Note
The trace event IDs remain the same for these event classes.
See Also
Concepts
Showplan Security
Logical and Physical Operators Reference
XML Showplans
Transact-SQL Statements That Produce Showplans