Analyzing Queries with SHOWPLAN Results in SQL Server Profiler
You can add Showplan event classes to a trace definition that cause SQL Server Profiler to gather and display query plan information in the trace. It is also possible to extract Showplan events from the other events collected in the trace and to save these Showplan events in a separate XML file.
Extracting Showplan events from the trace can be done in any of the following ways:
At trace configuration time, using the Events Extraction Settings tab. Note that this tab does not appear until you select a one of the Showplan events on the Events Selection tab.
Using the Extract SQL Server Events option on the File menu.
By extracting and saving individual events by right-clicking a specific event and choosing Extract Event Data.
Showplan Events
The Showplan trace events are listed and described in the following table.
Event name |
Description |
|---|---|
Performance statistics |
Indicates the first time a compiled Showplan is cached, when it is recompiled, and when it is dropped from the plan cache. The TextData column contains the Showplan in XML format. For more information, see Performance Statistics Event Class. |
Showplan All |
Displays the query plan with full compilation details of the executed Transact-SQL statement. For example, it might display costing estimates and column lists. 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 Statistics Profile |
Displays the query plan with full run-time details of the Transact-SQL statement being executed, including the actual number of rows passing through each operation. For more information, see Showplan Statistics Profile Event Class. |
Showplan Text |
Displays as binary data the query plan tree of the Transact-SQL statement being executed. For more information, see Showplan Text Event Class. |
Showplan Text (Unencoded) |
Displays as text the query plan tree of the Transact-SQL statement being executed. This event class displays the same information as Showplan Text, except that this event class displays text instead of binary data. For more information, see Showplan Text (Unencoded) Event Class. |
Showplan XML |
Displays the query plan with full data collected during query optimization. This event is generated only when a query plan is optimized. For more information, see Showplan XML Event Class. |
Showplan XML For Query Compile |
Displays the query plan when the query is compiled. For more information, see Showplan XML for Query Compile Event Class. |
Showplan XML Statistics Profile |
Displays the query plan with full run-time details in XML format. For example, this event class captures the number of rows passing through each operator of the Transact-SQL statement that is executed. For more information, see Showplan XML Statistics Profile Event Class. |