Plan Explorer - Fixes in the October 2017 Release
I'm happy to announce a new build of SentryOne Plan Explorer, with several minor fixes and enhancements. This is my first true release as Product Manager, and I could not have done it without the near heroic efforts of my development team and our supporting cast. It's also just in time for the two biggest conferences of the year, PASS Summit and SQLintersection.
A little over a year ago now, we consolidated our product offering and rebranded to SentryOne. SentryOne reflects our broadened vision of the entire Microsoft Data Platform. At the time, the focus was on our core solution; now, we have put Plan Explorer through the same branding transformation. So you will see SentryOne Plan Explorer throughout the UI, and the program has moved to (by default):
C:\Program Files\SentryOne\SentryOne Plan Explorer\SentryOne Plan Explorer.exe
Here's the bad news: If you've created custom shortcuts (including pin to start / taskbar), you'll have to pin the programs again. We're really sorry about that, and it's a long story I'm happy to share over a beer or chat if you're really interested.
The minimize bug
Sometimes, fixing the “stubborn little bugs” can be more annoying than the original symptom. Like ants at a picnic, they are mostly a benign nuisance, but difficult to get rid of. As silly as it sounds, one of these stubborn little bugs was easily our proudest achievement of this sprint. The short version is that, in some cases, if you minimized Plan Explorer and then tried to maximize from the taskbar, the application would not respond. I blogged about this issue back in June, including a few customer-furnished workarounds. Ever since, we have made several attempts to fix it, which was problematic due to unreliable repro steps. Today, I'm happy to say these workarounds are no longer necessary!
Index Analysis improvements
The bulk of changes here revolve around affixing schema (and sometimes database) prefixes to object names where relevant. We also added the estimated cost label to each operation in the drop-down, and now sort the operations by highest estimated cost first.
In the future, I'd like to enhance this in two ways: (1) to show actual costs when you've generated an actual plan, and (2) to allow you to choose whether to rank the costs by CPU, I/O, or the current mix of the two.
In some cases we'd have long lists of values in a tooltip, and they could make the tooltip stretch to the width and/or height of the screen. Now, instead of showing every individual value, we'll show the first two, the last two, and indicate how many values fell in between. The full list of values will still be available in the plan XML. In recent versions of SQL Server, new warnings exist in showplan XML (such as Memory Grant warnings). The updated Plan Explorer tooltips include these new warnings.
File/Open now supports opening multiple files at once, and you can filter to all supported file types. This means you can now open two .sqlplan files, a .queryanalysis file, three .pesession files, and a deadlock file - all in one single open operation.
Stop button now more prominent
The initial implementation of the Stop (cancel query) button was in the status bar. This has been made more discoverable on the application's main toolbar, to the right of the Get Actual Plan button:
While compile time has always been available in the status bar, we've now added it to the statement tree, so that you can sort / filter based on compilation times.
More info on wait types
When you collect an actual plan that reports wait statistics, each wait type will be linked to the appropriate page on the SQLskills Wait Types Library. This will help you further analyze which waits are an issue, and even compare to other users in the community to see how prevalent a specific wait might be.
With Columnstore indexes, Statistics I/O now shows us how many rowgroups were read and skipped (though they erroneously call them "segments"), and we now show these metrics on our Table I/O tab. This can be useful to see whether you're achieving rowgroup elimination, and we highlight when no rowgroups have been skipped:
Note: We can't show database/schema information here until Microsoft enhances Statistics I/O output.
A new start page experience
Finally, we overhauled the start page to give you more relevant info, and to better promote our paid features that are likely to be interesting to folks tuning queries. We pledge to keep this content fresher over time, including relevant, bite-sized tips and tricks, and we also plan to furnish a static page for those using the product disconnected.
Is there a feature you'd like to see in Plan Explorer? Some enhancement that would improve your workflow or make your tuning sessions more effective? Which SQL Server 2017 features do you want to see supported first? Let us know at firstname.lastname@example.org!
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.