Skip to content

Latest commit

 

History

History
44 lines (33 loc) · 4.8 KB

analyze-queries-with-showplan-results-in-sql-server-profiler.md

File metadata and controls

44 lines (33 loc) · 4.8 KB
title titleSuffix description author ms.author ms.date ms.service ms.subservice ms.topic
Analyze Queries with SHOWPLAN Results
SQL Server Profiler
Find out how to extract Showplan events from a trace so that SQL Server Profiler displays query plan information. View a table of Showplan trace events.
markingmyname
maghan
03/14/2017
sql
profiler
conceptual

Analyze Queries with SHOWPLAN Results in SQL Server Profiler

[!INCLUDE SQL Server Azure SQL Managed Instance] You can add Showplan event classes to a trace definition that cause [!INCLUDEssSqlProfiler] 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 [!INCLUDEtsql] 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 [!INCLUDEssNoVersion]. 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 [!INCLUDEtsql] 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 [!INCLUDEtsql] statement being executed. For more information, see Showplan Text Event Class.
Showplan Text (Unencoded) Displays as text the query plan tree of the [!INCLUDEtsql] 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 [!INCLUDEtsql] statement that is executed. For more information, see Showplan XML Statistics Profile Event Class.

See Also

Performance Event Category