Capturing Graphical Query Plans with SQL Server Profiler

By: Greg Robidoux | Read Comments | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More

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 2000 and 2005 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 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 Profiler versus SQL 2000 Profiler.  Below is the difference of events that can be captured using SQL Server 2000 versus SQL Server 2005.

SQL 2000 Performance Events

SQL 2005 Performance Events

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 2005 database and select these two events:

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.

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

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.

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.

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 now 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

• When troubleshooting a performance problem remember that you have the ability to get query plan information.  Now with SQL 2005 you can get also graphical plan as well as a text plan.
• When using these options be aware of the space requirements needed to capture and store all of this additional data.
• Try to filter the output based on the columns that are available for the event, so you do not collect too  much information
• Take a look at these other tips on setting up and running traces:
  • SQL Server Performance Statistics Using a Server Side Trace
  • Correlating Performance Monitor and Trace Data
  • How To: Graphical Deadlock Chain

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code