THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

Aaron Bertrand

Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at user group meetings and SQL Saturday events.

SQL Sentry Plan Explorer : New release and an SSMS add-in

Today we released a new build of SQL Sentry Plan Explorer. The build # is 6.2.34 and you can download it from the usual location.


Plan Explorer Changes

The updates in this release include the following:

  • A new "Check for Updates" feature accessible via the Help menu:
  • Other new additions to the Help menu:

    • Enable/disable the Check for Updates on Startup option
    • Enable/disable the warning message box when generating an actual plan

  • Plan diagram tooltips now indicate whether the operation is ordered:



  • There is a new theme in place for all grids, more consistent in both appearance and functionality with the grids in Event Manager / Performance Advisor.

  • We've added support for a new Management Studio add-in (see below).

  • Various fixes as outlined in the change list.


SQL Server Management Studio Add-In

In addition to an updated version of Plan Explorer, we have also released a free add-in for SQL Server Management Studio. What this does is allow you to launch an execution plan directly into Plan Explorer from within SSMS. To get this functionality:

  1. Download and install the latest version of SQL Sentry Plan Explorer from the following page:
    http://sqlsentry.net/plan-explorer/sql-server-query-view.asp
     
  2. Shut down all instances of Management Studio (note that the Visual Studio-integrated version of SSMS that ships with the Denali CTPs is not supported at this time, due to changes in the add-in model).
     
  3. Download and install the SQL Sentry Plan Explorer Management Studio Add-In, also from the following page:
    http://sqlsentry.net/plan-explorer/sql-server-query-view.asp
     
  4. Generate an actual or estimated plan in Management Studio, then right-click anywhere in the plan. You will see a new menu item entitled, "View with SQL Sentry Plan Explorer":



    This will launch Plan Explorer with your plan(s) in view and with the right connection context in place. This means that once you are in Plan Explorer, you can make changes to the query and generate new estimated or actual plans, and you won't need to fill in connection properties.

Questions

The first question I anticipate is, "Why isn't the add-in bundled as part of Plan Explorer's setup?" There are a couple of reasons. One is that not everybody using Plan Explorer will necessarily want to use the add-in. Another is that it requires SSMS to be shut down in order to replace the DLL. Since it is quite simple and not expected to be updated as frequently as Plan Explorer, the development team preferred a one-time install over disrupting the SSMS experience for each future version of Plan Explorer.

The next question I anticipate is, "When will the add-in support Denali?" I don't have an answer for this one just yet. But if you want some information about how much the add-in model has changed in the transition to a Visual Studio-based shell, you can ask @MladenPrajdic, the creator of SSMS Tools Pack. :-)

 

Published Wednesday, August 03, 2011 9:25 AM by AaronBertrand

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Luke Campbell said:

Downloaded.  Much appreciated.  I do get an error when attempting to return the actual plan from a SQL Server 2008 R2 instance.  The error is "A query plan was not collected at the time of statement execution.  Estimated plan generation is only available for non-dynamic root statements on SQL Server 2005 or higher".  The query executed with a user database was "select top 5 * from sys.tables".  I'm liking the integration with SSMS!

August 3, 2011 9:52 AM
 

Adam Machanic said:

Awesome. Can't wait to finally dig in to Plan Explorer now that I don't have to play games with saving files, etc.

August 3, 2011 10:30 AM
 

AaronBertrand said:

@Luke,

That is not the behavior I'm seeing. Is this true for all queries or just this specific one?

@Adam,

This was the only thing stopping you from using Plan Explorer? You know that there are other ways to get a plan into Plan Explorer without the add-in and without saving files, right?

August 3, 2011 10:36 AM
 

Jeffrey Langdon said:

The SSMS add in feature is greatly appreciated. Very nice! Thanks.

August 3, 2011 10:47 AM
 

Adam Machanic said:

No, I was not aware that there were other ways. I tried Plan Explorer twice, both times saving out the files. And while I liked what I saw, I didn't find it valuable enough to warrant the additional time/effort. So yes, that was the only thing stopping me from using it.

August 3, 2011 10:48 AM
 

AaronBertrand said:

@Adam, that's a shame because the documentation has always outlined ways to do this without bothering with files, including (a) right-click, show XML, copy, paste and (b) generating an estimated or actual plan from command text within Plan Explorer.

Well, hopefully you'll make up for lost time.

August 3, 2011 10:55 AM
 

Luke Campbell said:

Aaron, no errors are seen if the same query is executed after starting plan explorer. However, if i generate the plan from within SSMS and then view it in plan explorer is seen. The issue here is "user error" :).  Just noticed the command text is blank when using this method.  Thanks again!

August 3, 2011 11:06 AM
 

Paul White said:

It bugs me that Plan Explorer always reports the Partitioning Type for non-exchange parallel plan operators as 'Broadcast'.  Little things like that make me wonder how much I can rely on the accuracy of this tool.  Then again, I doubt my requirements are typical :)

I also used to find the need to do things like display the XML show plan (then copy & paste) inconvenient, so I might give the SSMS add-in a go.

August 3, 2011 11:10 AM
 

bonskijr said:

the "Display in Plan Explorer" is very handy indeed, it practically made the copy+paste from SSMS obsolete :)

August 3, 2011 12:24 PM
 

Jonathan Kehayias said:

Paul,

Have you reported the Partitioning Type issue that you mention above to SQLSentry?  I've submitted a ton of items since the private Alpha testing release that have all been implemented, fixed, or changed in the product, which is quite amazing for a free tool.

August 3, 2011 12:49 PM
 

Brooke Philpott said:

Paul, I've been informed of the issue with the Partitioning Type and we're working on a fix. Thanks for the bug report. The root problem was just loading the default value when indeed it wasn't specified in the plan. We've gone through every other possible place where this was the case and addressed all of them. I think in general you should find the accuracy of the tool unparalleled. There are in fact a number of much more obvious bugs with the way that SSMS renders plans that we address in our tool. We'll let you know when the new version is available. It should be soon.

Keep the bug reports coming as you find them. We will respond. Thanks again.

August 3, 2011 2:19 PM
 

Paul White said:

Hi Brooke,

Thanks for that - it's bugged me for a while.  Aaron and I had a good discussion offline about this, and other things too.  I'd be interested to read more about the SSMS bugs you mention.  Perhaps they're on your site, I'll check shortly.

Paul

August 3, 2011 3:02 PM
 

AaronBertrand said:

Paul, the user guide has a lot of SSMS' shortcomings highlighted. I wouldn't necessarily call them all bugs, but rather unfortunate design decisions. :-)

http://downloads.sqlsentry.net/downloads/docs/Plan%20Explorer%20User%20Guide.pdf

August 3, 2011 3:17 PM
 

Alex Friedman said:

No more copy-pasting XMLs? Awesome!

August 4, 2011 1:53 AM
 

subhan said:

Mr Aaron,

I have a package (Integration Service) in SQL Server 2008R2 (NO SP).

So, I take thats package to other pc with SQL Server 2008R2 SP1. And then i am modification,add some components. The result,

I running in SQL Server 2008R2 SP1, get 100 record (using flat file to oldb destination)

So I move thats package to  originally place (SQL Server 2008R2 NO_SP),and when execution the result 99 record.

not match..(i have try on some pc).

if i upgrading SQL Server 2008 (NO SP) --> SP, is there any possibility to bad impact existing package??

August 4, 2011 5:14 AM
 

John Alan said:

Downloaded and installed.

Doesn't work when looking at a plan via another route (Activity Monitor > Recent expensive queries > Show exec plan) but it's not a big issue.

Nice add-in!

August 4, 2011 8:15 AM
 

Jack Corbett said:

I'm going to love the SSMS add-in.

August 4, 2011 9:08 AM
 

AaronBertrand said:

subhan, is it possible that in one place you've configured column headers, and that accounts for the 100th row? In any case, this blog post is not about SSIS at all. I suggest you post your question to the MSDN forums or a site like askSQLServerCentral.com / StackOverflow.com / ServerFault.com ...

August 4, 2011 9:32 AM
 

AaronBertrand said:

John, it appears that when you load an execution plan from Activity Monitor, it associates a filename and path (the current login's documents directory), but doesn't actually *save* the file. This fools Plan Explorer into believing that the execution plan has been saved, and attempts to retrieve it from that path, and that explains the "There was an error loading the file..." issue.

You can work around this issue by clicking Save on the toolbar first... it's one extra step before using the context menu option, but still much easier than manually saving the file somewhere, then manually opening it in Plan Explorer.

You can validate the behavior by going to %USERPROFILE% and then double-clicking on the Documents folder (navigating to %USERPROFILE%\Documents\ directly lands you in My Documents, not Documents). Now view an execution plan as described, and notice that the .sqlplan file is not in that folder (so trying to load it with Plan Explorer will fail, whether you use the context menu or try to find the file with File/Open). Click on Save and the .sqlplan file appears (and now loading it with Plan Explorer will succeed as well).

August 4, 2011 11:05 AM
 

Brooke Philpott said:

Aaron is exactly right. This is what is tripping it up. The fix is easy and I have it working internally. Stay tuned.

August 4, 2011 1:25 PM
 

AaronBertrand said:

I've filed a Connect item for the Activity Monitor behavior:

http://connect.microsoft.com/SQLServer/feedback/details/683031/ssms-activity-monitor-execution-plan-implies-that-sqlplan-file-is-saved

August 4, 2011 1:58 PM
 

Sentry Plan Explorer add-in for SSMS! » SQL Server and Beyond said:

PingBack from http://sqlserverandbeyond.com/2011/09/sentry-plan-explorer-add-in-for-ssms/

September 20, 2011 10:27 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About AaronBertrand

...about me...

This Blog

Syndication
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement