THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Making Use of Plan Explorer in my own Environment

Back in October 2010, I briefly blogged about the SQL Sentry Plan Explorer in my blog post wrap up for SQL Bits 7 and how impressed I was with what I saw from a Alpha demo standpoint from Greg Gonzalez (Blog|Twitter) while I was at SQLBits 7 in York.  To be 100% honest and transparent, Greg gave me early access to this tool after discussing it at SQLBits 7, and I had the opportunity to test a number of pre-Beta releases where I was able to offer significant feedback and submit bugs in the tool to Greg and the primary developer of the tool with SQL Sentry before Greg ever announced the public beta of the tool on his blog. 

Today I had the opportunity to work with a vendor of mine on a performance problem and during the investigation of the problems, I identified a query that if rewritten a different way, without any index changes impact to the SQL Server was significantly lower.  To show this to the vendor I got on a WebEx session and shared my desktop with the vendors developers and executed their original query and my rewritten version of the query to show that my version output the same result and had significantly less impact to the server than their original one did.  They could see this in the execution times of the two queries, but to really drive the point home I did a copy/paste of the Actual Execution Plan XML from the SQL Batch that contained both statements in it to the SQL Plan Explorer for the vendor.  The output from SQL Plan Explorer is shown below.


The highlighted statement above shows the impact of the vendors original statement, while the one below it shows the impact of the query rewrite that I did while tuning this statement.  The rewrite that I did reduced the query execution time from 24 seconds to 4 seconds with the exact same output.  After doing some indexing analysis and adding two indexes to the database for this regularly executed query, the execution time was under 300ms, and the vendor is looking at making changes to their database to accommodate these changes.  However, the real win here was that the tool provided me with a way to show the two queries to the vendor to show their impact directly to the SQL Server.  Funny enough, the vendor developers had never actually heard of this tool, and downloaded it while we were on a conference call today to give it a whirl.

So how exactly did I do this?  It is really much easier than you might think.  To do this kind of side by side analysis, all you have to do is open a new query window in SQL Server Management Studio and paste both of the queries into the window.  Then turn on the Include Actual Execution Plan from the toolbar button as shown below:


When you execute the Batch, both of the plans will be output in the Execution Plan, and then you can click on the Execution Plan tab to view the Execution Plans together in SQL Server Management Studio.  To capture this and bring it into the SQL Sentry Plan Explorer, you can right click on the execution plan in SQL Server Management Studio and select the option to Show Execution Plan XML, which will open the Execution Plan in XML format in Management Studio.


One of the best features of the SQL Sentry Plan Explorer is that you can paste the Execution Plan XML into the tool to get a different perspective of the information contained in the XML from SQL Server Management Studio.  In the interests of protecting this vendor and their application, primarily because I value my job and I actually have a lot of respect for this vendor because they have been really amicable to work with over the last six months, I am going to mask any identifying information the example screenshots provided in the remainder of this blog post.  The following is what SQL Server Management Studio presents for the Actual Execution Plans of the two queries:


and here is the SQL Sentry Plan Explorer display for the two queries:


Note that the graphical execution plan only shows one of the statements from the batch, the selected statement in the upper part of the window.  Also note that the upper window shows the difference between the two statements that exist in the batch.  Now previously I stated that I showed the vendor the output from the Actual Execution Plan, but if you note in the above, the output only shows the Estimated Execution Plan information.  Unfortunately, at the point that I wrote this blog post, I didn’t have the Actual Execution Plan information available, and I couldn’t subject my production systems to the execution of these statements to write this blog post so I have had to use Estimated Plans that were edited to write this post.

In this post I have shown just one of the many benefits that is offered by the SQL Sentry Plan Explorer to production support DBA’s.  In my next few posts I will highlight a number of the other features of this free tool that a production support DBA will find useful in their regular work.

Published Wednesday, February 2, 2011 11:50 PM by Jonathan Kehayias



Joe Webb said:

Nice write up Jonathan! I'm looking forward to the next posts.


February 3, 2011 7:15 AM

Chris Wood said:


I have been testing this tool and it is so much better than the natively produced diagram from SSMS and appears to be getting better all the time.

With its ability to produce diagrams and plan trees and sortable column displays and query columns and parameter lists it beats the pants off of the SSMS offering.


February 3, 2011 1:03 PM
Anonymous comments are disabled

This Blog


Privacy Statement