Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Capturing Graphical Query Plans with SQL Server Profiler


By:   |   Last Updated: 2018-06-02   |   Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Query Plans

Problem

One of the primary things that any DBA or developer will do when tuning their SQL statements is to look at the query plan or execution plan.  This can be done in both a graphical tree format as well as a text based format.  For most people this information is confusing at best and does not explicitly tell you what the issue is and how to fix the problem.  For this reason, people have probably become more use to using the graphical plan output versus the text based output. 

When troubleshooting a performance problem, the first tool of choice is to either run a server side trace or a trace using Profiler.  In addition to getting the query stats it is sometimes helpful to also get the query plan, so you can see if a different plan is being used when the query runs in the application versus running the same query in a query window. When running a trace in SQL Server you have the ability to collect a text based query plan, but unless you are use to reading this output it is difficult to decipher what is going on especially for complex queries.  So how can you get useful query plan information in a graphical format when running a trace?

Solution

SQL Server 2005 and later offers the ability to capture query plan information in both a textual format or a graphical format.  When setting up your trace you have the ability to capture additional performance type events using SQL 2005 and later Profiler versus SQL 2000 Profiler. 

Below is the difference of events that can be captured using SQL Server 2000 versus SQL Server 2005 and later.

SQL 2000 Performance Events

Event Event Description
Degree of Parallelism (7.0 Insert) Describes the degree of parallelism assigned to the SQL statement for inserts.
Degree of Parallelism (7.0 Update) Describes the degree of parallelism assigned to the SQL statement for updates.
Degree of Parallelism (7.0 Delete) Describes the degree of parallelism assigned to the SQL statement for deletes.
Degree of Parallelism (7.0 Select) Describes the degree of parallelism assigned to the SQL statement for selects.
Execution Plan Displays the plan tree of the SQL statement being executed. The Showplan Text (Unencoded) event class is the same as the Showplan Text event class, except the event information is formatted as text rather than as binary data.
Show Plan All Displays the query-plan with full compile-time details (including costing estimates and column lists) of the SQL statement being executed. When the Showplan All event class is included in a trace, the BinaryData data column must be selected. If it is not, information for this event class will not be displayed in the trace.
Show Plan Statistics Displays the query-plan with full run-time details (including actual number of rows passing through each operation) of the SQL statement, which was executed. When the Showplan Statistics Profile event class is included in a trace, the BinaryData data column must be selected. If it is not, information for this event class will not be displayed in the trace.
Show Plan Text Displays the query-plan tree of the SQL statement being executed. Include the Showplan Text event class to identify the Showplan operators on SQL Server 2000 or SQL Server 7.0.

SQL 2005, 2008, 2012, 2014, 2016, 2017 Performance Events

Event Event Description
Auto Stats Collects the events associated with the automatic creation and updating of statistics.
Degree of Parallelism Describes the degree of parallelism assigned to the SQL statement.
Performance statistics Indicates that a compiled plan has been cached for the first time, recompiled, or evicted from the plan cache.
SQL:FullTextQuery Displays performance information about individual Full-Text queries, including the query itself, the number of rows returned, and the duration of the query.
Showplan All Displays the query-plan with full compile-time details (including costing estimates and column lists) of the SQL statement being executed. When the Showplan All event class is included in a trace, the BinaryData data column must be selected. If it is not, information for this event class will not be displayed in the trace.
Showplan All for Query Compile Occurs when SQL Server compiles a SQL Statement. Include the Showplan All for Query Compile event class to identify the Showplan operators on SQL Server 2000 or SQL Server 7.0. This event class will also work on SQL Server 2005 or later, however the information included is a subset of the information available in Showplan XML For Query Compile.
Showplan Text Displays the query-plan tree of the SQL statement being executed. Include the Showplan Text event class to identify the Showplan operators on SQL Server 2000 or SQL Server 7.0. This event class will also work on SQL Server 2005 or later, however the information included is a subset of the information available in Showplan All, Showplan XML Statistics Profile or Showplan XML.
Showplan Text (Unencoded) Displays the plan tree of the SQL statement being executed. The Showplan Text (Unencoded) event class is the same as the Showplan Text event class, except the event information is formatted as text rather than as binary data.
Showplan XML Occurs when SQL Server executes a SQL Statement. Include the Showplan XML event class to identify the Showplan operators. The event class stores each event as a well defined XML document.
Showplan XML For Query Compile Occurs when SQL Server compiles a SQL Statement. Include the Showplan XML For Query Compile event class to identify the Showplan operators on SQL Server.
Showplan XML Statistics Profile Occurs when SQL Server executes a SQL Statement. Include the Showplan XML Statististics Profile event class to identify the Showplan operators on SQL Server.

The events that we are interested in are the Showplan All and Showplan XML.  To see the output from each of these events we need to start a new profiler session for a SQL Server database and select these two events:

trace properties

Once these events have been select start the trace.

To get an idea of how this works and the output for these two different events we can run the following stored procedure against the AdventureWorks database.

USE AdventureWorks
GO
EXEC dbo.uspGetManagerEmployees 185

After this statement is run, we can stop the trace and we will get the following output from our trace:

event class

If we take a closer look at the Showplan XML output we get the following graphical query plan for this query.  This output works just like the output from a query window, so when you hover over one of the operations it gives you the details for each operation as shown below.

nested loops

If we take a closer look at the Showplan All output we get a lot more output in a different format.  Here are three screenshots from various parts of the output from the Showplan All event.

nested loops
logical
estimate rows

So as you can see the output from these two events is extremely helpful in trying to troubleshoot a performance problem.  For people that are more use to using the graphical output, SQL Server 2005 and later offers both methods of viewing the query plans.

From a first glance you would think this would be a great idea to just turn these events on for all activity.  That would be great, but be aware of the space needs to store all of this data.  From this simple example above the output files differ quite greatly.  For just this one statement here is the difference in file sizes.

  • 2K file - No query plan info
  • 18K file - Showplan All
  • 48K file - Showplan XML
  • 64K file - Both Showplan All and Showplan XML
Next Steps


Last Updated: 2018-06-02


next webcast button


next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools