Thursday, October 11, 2012

It's Time to Go PRO!

It's hard to believe that we released the first version of Plan Explorer almost two years ago! The acceptance it has seen by the SQL Server community during that time has been amazing, and tremendously gratifying for all of us here at SQL Sentry.

From the start we've had many requests for additional features and functionality. Many of those went directly into the free tool (for a full list go here). For others, it was obvious that some serious heavy lifting and/or an increase in the support requirements would be involved, making them not well-suited for a free tool.

Before I get into PRO, let me clarify something up front:

* The FREE version of Plan Explorer is not going anywhere *

We are simply rebranding it "Plan Explorer FREE", and it will continue to be enhanced, as it has been in this release.

PRO Features

Plan Explorer PRO is the result of many months of building out several of those high effort/high value features you've been asking for. Initially we've targeted the features that we believe will have the greatest impact on efficiency and information sharing. Here are the top 5 major features in PRO:

1) Multi-tab Interface

This is just as it sounds. You no longer need to launch a new version of the app for each query tuning session, you just open a new tab!

2) Session History Tracking

Multi-tab is great, but after using PRO daily for some time now, I cannot understate the value of history tracking. PRO tracks every new plan that's generated and auto-assigns it a version. With a single click you can go back to any version of a query to see how well your T-SQL optimizations or indexing changes are working:

history_tracking

You can easily add comments to each version via the dropdown or Comments pane. You also have the option of tracking only when the query text changes, which can reduce the number of versions created. I prefer to keep full tracking on, since generally when I generate a new plan and haven't changed the query text it's because I've made an indexing change, and I want to see the impact. I know I can always delete unwanted versions by right-clicking and selecting Delete.

You can save a query tuning session as a .pesession file for archiving or sharing. For example, if you're a consultant doing query tuning work, you can send the client the .pesession file to show all of the work you did to improve query performance, step-by-step. I've found that this is great for educational purposes as well, as I'm often trying to teach someone how to use Plan Explorer... now they're able to use .pesession files as a reference for future query optimizations.

History tracking not only saves the last statement selected in each version, but also saves the exact position and zoom level of the plan diagram for each statement! This is extremely helpful when you're dealing with large plans that require scrolling to a specific point – you can maintain that position and flip back and forth between versions for comparison, without any scrolling/zooming. All of this information is persisted in .pesession files.

Plan Explorer FREE is able to open .pesession files, but it can only view one plan version at a time, and will prompt you to select one upon opening.

3) Wait Statistics

For SQL Server 2008 and above, when generating an actual plan you'll see a new Wait Stats tab appear with the cumulative waits for that execution:

wait_stats

There is a lot of information out there on wait stats analysis, including this great post by Paul Randal (b|t), so I'm not going to delve further into that here.

4) Full Query Call Stack

This has been one of the true power features in SQL Sentry Performance Advisor for some time – I blogged about it here. Now it's available in Plan Explorer PRO at a fraction of the price.

Previously, when generating an actual plan with Plan Explorer the Statements Tree was effectively a flat list of statements, similar to how SSMS does it. In PRO you will see the full hierarchy of all statements with conditional logic and looping, and even statements called via dynamic SQL!

call_stack

5) Deadlocks

PRO contains another power feature from Performance Advisor: it can open deadlocks (.xdl files). You see the same awesome deadlocks view as in Performance Advisor – an optimized, sequenced deadlock graph synced with a grid with details for each node, including the query text:

deadlocks

This is also great for information sharing – if you're a DBA and use Performance Advisor, you can easily export deadlocks it captures and send them to the development team for analysis, without providing access to the full SQL Sentry Client. All they need is Plan Explorer PRO.

Other Features Shared with Plan Explorer FREE

There are quite a few fixes and minor features shared between PRO and FREE, including:

  • The new Status Bar at bottom which shows various connection details, query compilation and execution times, and actual rows affected.
  • The Command Text pane is now full screen! If you are a heavy user, you probably know how annoying it was to continually resize this pane as query changes were made.
  • Persistent connections. This is another big one. Previously, every time you hit the Estimated or Actual Plan buttons you'd be prompted for the connection. Now it works more like SSMS: it only prompts when needed, the connection details are shown in the status bar, and you can change the active connection at any time using Edit > Connection.
  • New Show Estimated Plan toolbar button. I kid ;-) This function has been there since we added the ability to generate actual plans, it was just well hidden behind a tiny little button... which was a shame since many people missed it, and the ability to toggle between actual and estimated plans is an invaluable feature when query tuning. It's now much more prominent on the toolbar. If you haven't used it before I encourage you to try it, as it can make inaccurate estimates at the operation level really jump out.

As you can see, Plan Explorer FREE is already benefitting from the increased effort we've put into making Plan Explorer truly more of a professional application... versus what we had before, which was effectively a big chunk of code extracted from Performance Advisor, with some "wrapper" code added to make it function as a standalone app.

Setup, Licensing, etc.

Both Plan Explorer FREE and PRO use the same codebase and setup. When you first launch Plan Explorer after install, you'll be prompted whether to go directly into FREE mode, or evaluate PRO for 15 days. This is not a persistent nag screen, you will only see it once after the initial install. However, if you decide to use FREE, you can always go back and start the PRO eval at any time using the Help menu.

For PRO, we've opted against charging annual software maintenance (ASM), and instead charge only a per seat license fee of $295 (volume discounts apply). This covers support, and any incremental releases for that major version. When the major version changes, e.g., when we release Plan Explorer PRO v2, there will be an optional discounted upgrade fee. This keeps things simpler for everyone. We won't be bothering you every year for ASM, and you decide whether or not it's worth it to upgrade. This also helps to ensure that we continue to add high value features to PRO ;-)

Please give the new Plan Explorer a try, and let us know what you think!

2 comments:

  1. Graphically, this is very impressive. I must be missing something though.

    I would think I should be able to quickly find Key Lookups and perhaps have the ability to create an Index ( or alter the existing index ) to fix this.

    Looking closer - it might be there - but I get 'The full version of SQL Sentry Performance Advisor is required to manage indexes. Visit www.sqlsentry.net for details.'

    How am I supposed to know what this will offer me before now buying 2 products?

    Do you have a video of this in action perhaps so I can see it diagnose and correct what it finds?

    Thanks,
    SQLOzzie

    ReplyDelete
  2. Hi Ozzie,
    If I understand what you're asking, yes, you can easily find lookups and determine which columns need to be covered, primarily using the Query Columns tab. See:
    http://greg.blogs.sqlsentry.net/2010/10/sql-sentry-plan-explorer-beta.html -> Query Columns topic

    We don't yet allow you to enable editing indexes directly from Plan Explorer, for technical reasons I covered here:
    http://greg.blogs.sqlsentry.net/2011/02/sql-sentry-v6-plan-analysis-features.html

    I will say that this feature and others that will make creating covering indexes much easier will be coming soon in Plan Explorer PRO. It will be a v1.x update, so if you purchase the current version you'll get the new features when they become available.

    Here are some videos:
    SQL Query Plan Analysis Made Easier: http://www.youtube.com/watch?v=nRb9dsdD-NE
    Plan Explorer Demo by Jonathan Kehayias (SQLskills): http://www.youtube.com/watch?v=0fDg3bSRjf0

    Also check out Aaron's blog:
    http://sqlblog.com/blogs/aaron_bertrand/archive/tags/Plan+Explorer/default.aspx
    ...and Paul White's blog as he uses Plan Explorer extensively:
    http://sqlblog.com/blogs/paul_white/

    I hope this helps!

    ReplyDelete