SQL Sentry v6 Plan Analysis Features

Almost a year ago, we embarked on the journey to SQL Sentry v6. This was not long after we had decided that our release cycles were too long, and I had just boldly proclaimed that there would be no more year-long cycles – we were shifting to smaller, incremental releases! They’re easier to test, they get new features in the hands of our users faster, they keep up momentum, morale, etc.

All of this is true, but looking back it probably wasn’t the right release to make that decision. 😉 At the time, our vision of plan analysis was hooking into SSMS – like we do in other parts of the app – in order to render the graphical plan diagram for statements captured via our “Top SQL” subsystem (a lightweight trace that runs continuously and catches “big hitter” queries). I’m not sure of the exact point at which we ditched that idea in favor of rolling our own plan analysis capability, but I remember it happened very quickly. It was probably right after our lead developer Brooke Philpott (blog|twitter), never one to shy away from a development challenge, threw together a quick prototype showing that we could indeed parse the plan XML and render a graphical tree, among other things. Next thing you know, what would have taken a few weeks took over 10 months!

That said, I’m glad we made the decision to build our own plan analysis system, and would do it again. We’ve received tremendous feedback since releasing the free Plan Explorer, and we’re thrilled that it’s making a real difference for SQL Server users.

FREE Plan Explorer vs. Performance Advisor v6

This is something we’ve been asked about a lot lately. Notice I didn’t say “… vs. FULL Plan Explorer.” That’s because there is no “full” version of Plan Explorer per se; it’s all the same code. Rather there is an “integrated” version that resides within the SQL Sentry Console, and it has additional capabilities that open up when you have a SQL Sentry Server service and database installed. There are also other plan analysis features included in v6 that exist completely outside of Plan Explorer.

You may have noticed that there are some grayed out columns in the free Plan Explorer statements tree, and when you hover over them you get this message:

No, this isn’t some cheap attempt by marketing to lure you in and then force you to buy the full software. Rather, this is the development team telling you that what you are trying to do isn’t technically possible… unless you have a SQL Sentry service and database. Which, if you did, would mean that Plan Explorer was no longer a tiny download with simple installation and minimal footprint. Sure, we want you to see what you’re missing in the full software, but it was easier and less invasive to just gray columns and/or throw up a message box than add new code paths to selectively show/hide features.

When we unbolted Plan Explorer from the full software, anywhere there was something we just couldn’t do, we added this note. Another place you’ll see this is if you try to create or edit an index, since that would require SSMS integration, as well as the ability to determine the location of the server and database in question (since they aren’t stored in a plan), and then authenticate. This would mean additional development and testing, new external dependencies, and opportunities for something to break.

As an example of what we deal with when integrating with SSMS, in SQL Server 2008 R2 some of our code – which worked perfectly in 2008 – suddenly ceased to function, but only if the SSMS Object Explorer was unpinned!? It took us a while to figure that one out. So index management is something technically we could pull off, and we may add to the free Plan Explorer at some point, but it’s honestly not at the top of the list.

Active Plan Capture

When you install SQL Sentry v6, you gain what we call active plan capture, and all of the new plan analysis features depend on this. The Server service continuously monitors running queries and completed trace events and, when appropriate, queries the plan cache for plan XML and/or other plan details, and automatically assembling all of the pieces together. This enables us to do several things that aren’t possible with the free Plan Explorer, or any other available software for that matter:

• Capture a query’s plan at the time of query execution and associate it with the query.
• Assemble the entire call stack for a query, including any nested stored procedure and/or nested dynamic SQL calls.
• Show statement level metrics from the execution plan side-by-side with trace and DMV metrics.
• Keep a history of all plan versions used by a query over time, so you can see where plan changes impact performance.

I’ll go into each of these in more detail below. If you want to know more about the graphical plan diagram and other shared Plan Explorer features first, check out this post or Aaron Bertrand’s (blog|twitter) Plan Explorer posts.

The Query Plan at Query Execution

With the free Plan Explorer version, it’s up to the user to identify problem queries, somehow capture the estimated or actual plans, and then manually load them in Plan Explorer. The full version of Performance Advisor does all of this for you. Heavy queries (batches or RPCs) are continuously picked up by the Top SQL trace as normal, based upon the filter settings you provide (“Duration > 5 seconds” by default), and plan information is immediately captured and associated with the queries. The only thing you need to access the plan is click the View button in the new Plan column:

As you can see, this column exists for both completed and running queries… so yes, you can view the plan while a query is running!

It’s important to note that this is the estimated plan at time of query execution, not the actual plan including actual rows, executions and data for each plan operation. It is not currently possible to capture the actual plan continuously in a low overhead fashion. (No, not even with Extended Events) 😉 We pretty much knew this going in, but we spent a fair amount of time trying some different approaches to pull it off anyway, to no avail. There is some hope that this will be possible in a future version of SQL Server, however. That said, the estimated plan at query execution is certainly the next best thing, and even has some advantages which I’ll delve into in a future post.

I’ve manually added a couple of columns to the Completed Queries grid above: Has Plan and Has Statements. These are hidden by default, but are handy for quickly identifying batches and RPCs for which we were able to capture a plan and/or statements, which will impact what you see when you click “View.” Depending on the type of query, timing, and other issues, it’s not always possible to capture the plan. It’s also possible to capture a batch or RPC with no statements, if none exceeded the trace filter thresholds (this has always been the case).

A Quick Aside: Other Top SQL Enhancements

Long-time Performance Advisor users will probably notice some other differences in the above Top SQL shot, so I’ll touch on them briefly:

• The Running Queries pane – we now show you heavy queries (again based upon your filter settings) while they are running, not just after they complete.
• The Filter pane is hidden by default.
• Statement events (SQL:StmtCompleted or SP:StmtCompleted events) are no longer shown here, only SQL:BatchCompleted and RPC:Completed events. This is because they are now integrated into the Plan Explorer view. More on this later.
• Text Data (TSQL, MDX and XMLA) is now colorized.

Some additional changes to Top SQL that aren’t apparent above:

• Auto-growth and Auto-shrink events are now captured. Both can be a hidden performance killer and can bring activity to a screeching halt while file space is adjusted.
• New columns have been added:
  • Error – shows the general reason a query terminated prematurely. Rows with errors are also highlighted with a light red background.
  • Information – shows either “rows affected” or “auto-growth size” depending on the event.
• Blocked spids are shown with a light orange background in the Running Queries pane.

The Query Call Stack

A Profiler trace presents a stream of trace events, and there is little to no sense of how the events are related to one another. Throw in nested procedure calls and dynamic SQL, and all bets are off.

This is why one of the most significant features gained with the full software is the query call stack assembled in real time. This lets you see exactly how captured statements are related and where they fit into the query plan. Most importantly, it shows you which statements did the bulk of the work, and which are candidates for optimization.

Below is the Statements Tree pane for a stored procedure that calls some dynamic SQL via sp_executesql, which calls another stored procedure, which proceeds to call more dynamic SQL:

Plan Explorer Statements Tree – Fully Assembled Stack (Performance Advisor v6)

I’ve highlighted the dynamic SQL calls in yellow, the nested procedure calls in light blue, and the final dynamic SQL generated in pink. Note that the parent procedure itself in this case is effectively glue; it’s the dynamic SQL that is doing the bulk of the work. If you were to look at the estimated plan in Plan Explorer, you’d see this:

Plan Explorer Statements Tree – Estimated Plan (free version)

If you click to enlarge you’ll see the initial dynamic SQL call in yellow at the very bottom. That’s the same line that makes the three initial dynamic SQL calls in the full stack shot above! Problem is, the compiled estimated plan has no visibility into what that dynamic SQL will actually look like, let alone how it will perform.

A big difference between the two views above is that the call stack shot is in Show Captured Statements Only mode, which is the default whenever we have at least one statement for a batch or RPC. In this mode you see only statements that exceeded the trace filters along with any relevant control structures, in this case an IF block and a WHILE loop. This mode can be toggled off via context menu item, but for complex procedures like this one it’s really invaluable, since it filters the “noise” and directs your attention to the heaviest parts of the query.

Correlated Metrics

You probably noticed another difference between the two views – one has Duration and CPU data and the other has those annoying gray columns. Here’s another view of a full stack with many captured statements:

When we have the actuals we’re able to fill in those other columns, as well as compare the estimated and actual rows affected by each statement. When there’s a significant discrepancy the cells are highlighted in orange, and if you hover over them you’ll see a tooltip with more details.

Execution Plan History

On the new Query Plans tab you’ll see all plans captured during the specified date range:

There are two modes, toggled using the “Show Totals” (sigma) toolbar button. One shows plans grouped by stored procedure (remember, starting in SQL Server 2005, with statement-level compilation there can be multiple plans per procedure), the other is a flat list of all statement plans. Both show average metrics and operation counts. For example, if you want to see the plan with the most “scan” operations over the last 2 days, simply set your range, sort by one of the “Index Scan” columns, then click the “View” button to open the plan:

The rows highlighted in red represent recompiles that occurred within the current date range. The other plans may have been used during the current range, but they must have been compiled earlier.

Here’s another really cool use case: Using the new “Jump To->Query Plans” context menu item from the dashboard to find problematic plans. For example, if you see a spike in “Bookmark lookups,” you can select it then jump to the plans tab to see only plans used during that range, then sort by “Lookup Operations” to find the plan(s) that may be responsible:

I should mention that “Bookmark lookups,” “Forwarded records,” and “Backup/Restore Throughput” are new to the dashboard in v6.

Performance Hit?

All of this looks great, but does the additional load on your servers and network outweigh the benefits of collecting and processing this information? Certainly not. As always, we’ve gone to extreme lengths to ensure that any hit caused by the new plan analysis features is minimal. Intelligent data collection, compression, and other techniques are in play to ensure the hit will be barely noticeable… not nearly enough to outweigh the performance gains you’ll reap from using the new features.

In Closing…

Going in, our goal with v6 was to present the most complete and accurate picture of a query possible – what it did and why. Other than a few aspects that technically aren’t achievable today, like continuously capturing the actual plans, I think we succeeded. I’d liken it to a 500-piece puzzle, fully assembled but with 50 pieces missing… which IMO is preferable to 450 pieces in the box, which is what we had before.

I’m really excited about this release and hope you are too! Take it for a spin and let us know what you think. Existing customers get it here.

Greg Gonzalez

Greg is founder and Chief Scientist at SentryOne. He is a Microsoft developer by background and has been working with SQL Server since the mid 90s. He is perpetually engaged in the design and development of SQL Sentry, Plan Explorer and other SentryOne solutions for optimizing performance on the Microsoft Data Platform.