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).
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:
- Download and install the latest version of SQL Sentry Plan Explorer from the following page:
https://sentryone.com/plan-explorer
- 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).
- Download and install the SQL Sentry Plan Explorer Management Studio Add-In, also from the following page:
https://sentryone.com/plan-explorer
- 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. 🙂
Aaron, nicely done. Like the latest version. Thanks!
I've filed a Connect item for the Activity Monitor behavior:
http://web.archive.org/web/*/http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/683031/ssms-activity-monitor-execution-plan-implies-that-sqlplan-file-is-saved
Aaron is exactly right. This is what is tripping it up. The fix is easy and I have it working internally. Stay tuned.
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).
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 …
I'm going to love the SSMS add-in.
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!
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??
No more copy-pasting XMLs? Awesome!
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
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
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.
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.
the "Display in Plan Explorer" is very handy indeed, it practically made the copy+paste from SSMS obsolete 🙂
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.
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!
@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.
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.
The SSMS add in feature is greatly appreciated. Very nice! Thanks.
@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?
Awesome. Can't wait to finally dig in to Plan Explorer now that I don't have to play games with saving files, etc.
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!