Wednesday, February 16, 2011

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:

full_version_tooltip

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:

topsql_view_plan

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:

call_stack
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:

call_stack_est
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:

combined_metrics

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:

queryplans-objectview

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:

queryplans-sort

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:

queryplans-jumpto

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.

Tuesday, February 8, 2011

SQL Sentry Sponsors Master Immersion, and 5 Reasons Why You Need to Go!

Unless you've been under a rock lately (or just really, really busy ;-) you're probably aware of the MCM program changes, as well as SQLskills new training format, Master Immersion, which takes their former Immersion training a step further, making it even more MCM-focused. Three of us attended the Boston Immersion event last year (me, Brooke Philpott (blog | twitter), and Aaron Bertrand (blog | twitter)), and so we know firsthand the high quality training it provides. I blogged about the experience here. Now what was already great training has become even better, and if you want to apply it to your quest for MCM certification, you can.

While demonstrating our software, particularly Performance Advisor, over the past several years I've made an interesting observation. It's that a person’s depth of knowledge about SQL Server tends to be proportional to their appreciation for the things our software does. This isn’t a knock on junior level SQL Server professionals because everyone starts somewhere. It's merely a statement that if you don't start with an understanding and appreciation of the underlying issues impacting SQL Server performance, then you'll inherently have less appreciation for why we're showing certain information, and the fashion in which it's being shown.

Hey, everyone wants to be understood and appreciated, right? ;-) So this realization is one thing that has led us to increase our support for various "newer" educational events over the past couple of years. From helping to organize a SQLSaturday here in Charlotte, sponsoring other SQLSaturdays, being one of the first U.S.-based companies to support (and attend) SQLBits, to sending 4 people on the inaugural SQLCruise. Now I'm not going to ask you to believe there is no promotional aspect to what we do, of course there is. We have to sell our software to remain viable. But we truly believe in our software – we know it has substantial real world merit, and we know it is quality through and through. We work too hard for it not to be. Just like SQLskills knows their training is the best, because they put everything they are into making it so.

When we heard about the new Master Immersion Events, we couldn't help but get excited. So much so that we approached Paul Randal (blog | twitter) and Kimberly Tripp (blog | twitter) and asked if there was any way we could be involved. One thing led to another, and I'm proud to announce that we are the exclusive sponsor of the first two Master Immersion events! We are providing a full breakfast and lunch for attendees all week, and some really cool swag! We're also sending three more SQL Sentry employees through the training.

As I said in my previous post, it doesn't matter where you lie on the SQL Server skill or experience spectrum, if working with SQL Server represents a significant portion of your job, you will benefit greatly from this training. If this same material was in the hands of lesser instructors this simply would not be the case, but Kimberly and Paul do an incredible job of presenting deep material in an easily digestible, stimulating fashion.

If you can break away for a week in February or March, do it, you won't regret it. In case you need help justifying it with your boss, spouse, kids, whoever, here are 5 reasons you need to go:

 

5. It is the best SQL Server training, from the best instructors in the business. Period.

4. It costs no more than a week of run-of-the-mill SQL Server training at your local IT training center... except that Kimberly Tripp, Paul Randal, and Brent Ozar (blog | twitter) will be teaching the class.

3. It will make you better at your job, which may lead to increased job security, pay, and praise from management and peers.

2. It doesn't last 3 weeks or cost $18,500, and there is no killer pass/fail test at the end of the week.

1. You won't have to pay for breakfast or lunch since SQL Sentry is picking up the tab!

 

Believe it or not there are still a few seats left for the Feb 21st class on Internals and Performance, as well as the March 14 class on Performance Tuning. So if you've been thinking about it, don't wait, get going and register now!