Plan Explorer PRO 2.5: Query plans your way

Have you ever worked with a query plan that was so large and unwieldy that it was next to impossible to make any sense of it? It can be a frustrating endeavor, for sure. Plan Explorer has always done some things that can help with these plans, such as color-scaling costs and vertically compressing subtrees. Even so, when you're zoomed out to 10% and still only fitting a quarter of the plan on screen, color-scaling won't buy you much.

The new version of Plan Explorer PRO 2.5 is focused on making these big plans easier to manage, and it does so using a variety of techniques that you have been asking for, which I will cover below. All of these new features are managed by this new toolbar at bottom of the plan diagram:

Operation Filtering

The quickest way to shrink most plans is to apply a cost filter using the Filter slider. This is just as it sounds – if an operator's (aka, iterator's) cost is less than the specified filter value, it will disappear from view. There is a caveat: any operators "upstream" in the same subtree as an unfiltered operator (one that exceeds the filter value) will still be shown, in an effort to avoid changing the meaning of the plan too dramatically. The net effect is that as you increase the filter value, op nodes will start dropping off at the leaf levels, working up towards the root node.

Here's a nice example using a plan Jonathan Kehayias (blog | twitter) shared with me a while back, aptly named "ReallyBadPlan.sqlplan":

Here is the original, with no filter:

... and with a .1% filter applied:

...and with a 2.3% filter applied:

As you can see, with big plans a little filtering can go a long way. The zoom level is reduced significantly, and the whole (filtered) plan is in view. The more op nodes, the lower the average cost per node, so this makes sense.

Collapsible Subtrees

Another way to quickly reduce a plan is by collapsing subtrees that aren't of interest. You do this via the new expander element on the head node of each subtree:

The expander's primary function here is to collapse, but "collapser" just doesn't have the same ring to it ;-)

Multiple Layout Modes

Some plan types simply don't fit well on screen, and this is where using a different layout mode can be helpful. It'll be easiest to illustrate this with a couple of common examples.

First, let me say that these "non-standard" plan layouts may be unsettling for some. If for whatever reason you are only willing or able to process plans using the traditional layout, my recommendation is this: don't use this feature, and skip to the next section now, lest you may have these images permanently seared into your brain!

Stair Step Plans

First up is a fairly extreme example of a "stair step" type plan, which I see most often with data warehouse queries against star schemas:

The plan is so vertically intensive that there is no way to get the entire thing in view and still have it be usable. By usable I mean in a state where I can clearly see the costs, colorization, line widths, operator glyphs, and other visual cues necessary for troubleshooting.

By changing to Alt-1 mode we can vertically compress this plan in dramatic fashion:

I haven't applied any filters, or changed any other settings, and as you can see it's already much more usable. The trick is being able to do a little neural rewiring such that the non-standard orientation of some of the nodes with respect to each other doesn't interfere too much with your interpreting the plan.

UNION Query Plans

With UNION queries you effectively have multiple individual plans in all their glory stacked on top of each other. This can make them quite tall due to the potentially large subtrees off of the concatenation operators, as in this plan:

Bus mode can sometimes help with this, like so:

With Bus, subtrees can be centered and staggered, taking better advantage of the horizontal screen real estate, typically a less precious commodity on modern displays.

Be warned, not every layout mode will work well with every plan. For example, Bus mode applied to the "Really Bad Plan" above will create something far nastier and far less usable than the original. Every plan is different, so you may need to experiment to find which layout works best.

Rotation

As Paul White (blog | twitter) describes in his post Iterators, Query Plans, and Why They Run Backwards, plan execution order flows left-to-right, which is likely why Microsoft decided to put the root node on the left. He also covers how data flows in the opposite direction, right-to-left. So if you are concerned about IOs, you may commonly start on the right, as I do. Since this is opposite of normal reading direction for many of us, a mirrored view may be easier to process. This can be accomplished by clicking either rotate arrow twice to get to 180 degrees:

If you change to Centered mode and click the right rotation arrow once to 90 degrees, the plan starts to look like an org chart, which may work better for those who read top-to-bottom (or who work in HR):

Dynamic Auto-fit

Your wrist will appreciate this feature – it automatically resizes new layouts to fit the screen, so you don't have to continually change the zoom level after applying filters or otherwise changing the plan layout. (Astute readers may notice that this checkbox is shown in some of the shots and not others – that's because it was added while I was writing this, and I didn't feel like redoing a bunch of shots ;-)

Persisting and Sharing Custom Layouts

Custom layouts are automatically persisted when saving a plan with Plan Explorer as a .queryanalysis or .pesession file, and these files can be easily shared by clicking the Post to SQLPerformance.com toolbar button, emailing, etc. Note that manually repositioned nodes are not currently persisted, but we're working on that.

Did I mention that you can now easily upload your plans to Answers.SQLPerformance.com from all versions of Plan Explorer, and receive input from some of the world's foremost experts on query plans, including Paul White (blog | twitter)? Aaron Bertand (blog | twitter) covers this feature in detail here.

Plan Explorer PRO users who don't want to use the custom layout in a shared plan can hit the Defaults button to reset everything. Plan Explorer FREE users can view, but cannot modify, Plan Explorer PRO custom layouts via the Apply Embedded Layout Options checkbox at bottom left of the diagram:

Closing

There are probably as many different optimal layouts as there are big plans. Our goal with this release was to provide more control over how plans are presented, so you can view them in ways that work best for you. You'll need to experiment to see which combinations of modes and settings work best for each plan. I've personally had a lot of fun playing around... even the really bad layouts can provide some entertainment value ;-)

Kudos to the dev team led by Brooke Philpott (blog | twitter) and QA team lead by Steve Wright (blog | twitter) for their great work here. Thanks also to those of you who have shared your ideas with us... this release is a direct result of that feedback.

Oh, and if you find settings that work particularly well for certain plan shapes, please do let me know: ggonzalez at sqlsentry.net. Enjoy!

SQL Sentry v7.2 does Windows (and a whole lot more)

Since releasing SQL Sentry v7 with Fragmentation Manager in May, we've been hard at work on v7.2. Development ran concurrently with Plan Explorer PRO, which was released last week. v7.2 includes two exciting new products: Performance Advisor for Windows and Event Manager for Windows. The release candidate was recently published, and you can download it from our customer portal. (Existing customers please contact us for eval licenses.)

The new features fall into three main categories: Query Plan Analysis, Windows Monitoring, and History/Alert Filtering.

Query Plan Analysis

As you may have guessed, the new version of Performance Advisor for SQL Server includes all Plan Explorer PRO features! Read about those in my last post.

Windows Monitoring

One of the most common requests we've received over the years has been for the ability to monitor any Windows computer, such as one running SharePoint, IIS, SSRS, or SSIS services, but no SQL Server or SSAS services. Performance Advisor for SQL Server has actually always had Windows monitoring built in – it's effectively the left-hand side of the dashboard – the problem was, you only got it if you also had SQL Server or SSAS installed on the machine.

I'm happy to say that not only has this restriction been removed in the new Performance Advisor for Windows, but when combined with the new Event Manager for Windows you now have truly unprecedented capabilities for monitoring Windows performance. Keep reading for a rundown.

Service & Process-level Metrics

To date, probably the most common way to find out what's happening with Windows processes has been via Windows Task Manager. Process Explorer is a more robust tool that is also used, however, both tools suffer from some critical shortcomings:

1. It's not easy to tell which Windows services are associated with which processes. Task Manager now has "Go to Service|Process" context menus, but in practical use they are not very helpful. Process Explorer requires a few clicks to view associated services. Most importantly, both tools are "one-process-at-a-time".
2. Lack of historical data. Process Explorer has some limited charting, but neither tool can show which process(es) brought a server to its knees 3 hours ago, let alone view the data in context with what was happening with SQL Server or other key metrics at the time.
3. No way to easily get performance metrics for individual Windows services, or combined metrics for multiple related processes/services. SharePoint and IIS in particular utilize several processes and services, and the problem may actually be the cumulative impact.

Performance Advisor for Windows addresses all of these issues by:

1. Auto-correlating processes with services.
2. Providing historical performance data for processes and services.
3. Organizing related processes into friendly groups, with individual and group level metrics.

In the dashboard shot below, the tannish series represents all SharePoint processes and services, and by hovering over it I can instantly see that two SharePoint search processes together were mostly responsible for the CPU spike:

If I want full details, I can simply select the range and then Jump To > Processes:

...which takes me directly to the new Processes tab, where I have a full list of all active processes and metrics for the range, organized by service and group:

(click to enlarge)

It's just as easy to find the largest memory consumers – here there are several IIS worker processes consuming over 1GB of RAM, and causing memory pressure for a SQL Server instance on the same machine (not a recommended configuration!):

Several groups come preconfigured, including SharePoint, IIS, SSIS, SSRS, and SQL Sentry, and we'll be expanding this list as time goes on. You can also easily add new groups yourself – more on that in a future post.

If, like me, you've spent way too much time using the "old school" ways of troubleshooting process/service-related performance issues, these new features should be a godsend.

Processor Groups + NUMA Support

With Windows Server 2008 R2, Microsoft introduced processor groups as part of support for more than 64 processors on a single computer. Lately we've been seeing more and more of these systems come online for both multi-instance SQL Server environments, as well as VMWare or Hyper-V hosts running tens or hundreds of VMs.

Previously the PA dashboard maxed out at 40 processors due to limitations in how perflib reported instance level data. Now the dashboard will show utilization across all processors on a system. Here's a shot of an 80-processor system (4 x 10 cores, hyperthreaded):

(click to enlarge)

If you look closely, you'll see that the NUMA node associated with each processor is indicated using the format <NUMA Node ID>:<Processor ID>. This system has 4 NUMA nodes, and here only nodes 1 and 3 are active. This information can be invaluable for evaluating how your NUMA resources are being utilized, and for judging the effectiveness of configured affinity settings.

Windows Event Log Monitoring

We've rolled the former Event Manager for Task Scheduler into the new Event Manager for Windows product, and added Windows event log monitoring, another hugely popular request!

You can selectively enable/disable monitoring for the Application, Security and System logs individually. Once enabled, events will show up on the calendar and become available for alerting via the new Windows Event Log: Event condition. By default, only the Application and System logs are watched by SQL Sentry, and only events from SQL Server or SQL Sentry services are collected using the new history filters.

History and Alert Filtering

Which leads me to our next topic: filtering. There are two types of filters – history (or source) filters and alert (or condition) filters. They're both configured the exact same way.

History Filtering

Many event sources now have a configurable filter which allows you to tell SQL Sentry exactly which events you want to collect and store from that source, giving you much greater control over associated storage requirements, which events will show up on the calendar, and which are available for alerting. You'll find this under Settings > History Filter for each source. This shot shows the new Windows Event Logs Source filter:

This is the default filter (as mentioned above) which collects only events for the SQL Sentry and SQL Server applications, and only non-backup events (since you'll often already have the associated job and Top SQL events on the calendar). We've made this pretty restrictive to start since the Windows logs can get flooded with events from various sources, and we didn't want to overload you with stuff you may not want to see. That said, you are free to modify these filters to your heart's content ;-)

Alert Filtering

You can think of alert filtering as a layer on top of history filtering. For example, using the history filter you may want to collect all SQL Server related events from the Windows logs and see them on the calendar, but you only want to be alerted for errors. This is trivial to configure by selecting the Condition Settings tab for the Windows Event Log: Event condition:

A really cool aspect of filter configuration is that you pick from a list of filterable fields applicable to that particular source, and if the possible values are known, you pick from those too. Here are some other common use cases:

• Only send an email for deadlocks NOT caused by a particular application (ahem, SharePoint ;-)
• Only send an SNMP trap to SCOM for SQL Agent job failures for jobs in a certain category
• Only alert for Top SQL failures coming from a specific host machine

These examples are only scratching the surface of what's possible with the new filtering. I'm always interested in hearing how new features like this are put to use, so please let me know about your favorite filters.

More than Windows?

Although I've been using "Windows monitoring" as a general term, these new features are especially applicable to some specific scenarios such as managing a SharePoint Server farm where you have different services split across many servers. You'll still have one or more SQL Servers playing a large role in farm performance, of course, but your visibility won't be limited to those servers... for the first time you can have a complete picture of performance across the entire farm!

We've published pricing for Performance Advisor for Windows, Event Manager for Windows, and the combined Power Suite for Windows here. The full product pages should be live by early next week.

We'll be showing off v7.2 in person over the next two weeks at DevConnections and then the PASS Summit, so please stop by our booth for a full tour!

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:

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:

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!

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:

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!

Kevin Kline joins the SQL Sentry Team!

Almost 10 years ago, SQL Sentry software was in its infancy, and was still used exclusively by our hosting business (now Peak 10). I remember clearly bringing the entire hosting team together one afternoon to watch a webcast on query tuning... a webcast put on by none other than Kevin Kline (b|t). At that point Kevin was already one of the most active and visible people in the SQL Server community, but I'd never heard him speak. I recall being impressed by the quality of technical content as well as Kevin's seasoned presentation skills... and this was back when webcasts of this type were not commonplace. Kevin was ahead of the curve. Several people in the room were more networking or hardware-focused, but Kevin was so good at distilling complex subject matter down to easily digestible essentials that even they left with new query tuning skills.

I didn't meet Kevin until a few years later when he came to Charlotte to present at a CSSUG meeting. I must admit I was a little nervous and maybe even a bit star struck going in. (Please don't tell Kevin ;-) I was half expecting someone arrogant, self-absorbed, and possibly even combative given that our companies were direct competitors by then. Boy, was I off the mark! Here was a guy that was friendly, humble and engaging, pretty much the opposite of what I'd anticipated... no sign whatsoever of any of the trappings common to years in the spotlight. (I'm sure that those of you that know Kevin are chuckling at my severe misreckoning)

When reading Kevin's bio, I can't help but feel humbled by the magnitude of his selfless efforts in the SQL Server community over the years. For a recap, I'd recommend checking out this SQLMag interview. If reading it makes you ask yourself, "What have I done lately to give back?", you are not alone. Kevin is one of the good guys, and we are extremely fortunate to have him as part of our team.

Not to worry, you won't be seeing any less of Kevin. I expect him to be just as engaged in the SQL Server community as he's always been, perhaps more so. Our goal is to tap into Kevin's unique abilities to the greatest extent possible, and enable him to get back to his roots in some respects. He's certainly going to be involved in some exciting community-focused initiatives here at SQL Sentry.

It's tempting to draw correlations with LeBron's move to the Heat – the initial shock felt by many, and the eventual benefit to both the individual and the team – but I will refrain ;-)

One thing is sure: although the uniform will be changing, it'll be the same Kevin you know and love underneath.

SQL Sentry v7 goes Gold!

After what has been a relatively smooth beta, we're going gold with v7 today! Read more about v7 features in my last post. I've also got some exciting news regarding new fragmentation-related features included in the base software, and how to get FREE licenses of both Performance Advisor and Fragmentation Manager.

New Base Software Features

Originally Fragmentation Manager was designed to be either ON or OFF, there was no in between. When it was OFF for a server, no table and index size or fragmentation-related data whatsoever was made available inside either Performance Advisor or Event Manager.

We've changed this, in a big way. A significant portion of the new features previously found only in Fragmentation Manager are now ON by default, whether or not you've purchased a license for it. Here's what you now get out of the box with v7, for both new installs and upgrades:

• New "Indexes" tab in Performance Advisor, with table/index size and other details
• Scheduled and manual fragmentation analysis with adjustable scan mode, from the SQL Server instance down to partition level
• Table/index size and other details on the Performance Advisor Disk Space tab, organized by data file
• Calendar display of all historical and future fragmentation analysis events, with drag-and-drop support
• New "Databases" node for each SQL Server instance in the Navigator pane, listing all table and indexes
• Partitioning sliding window support for fragmentation analysis (defrag only max partition, or all others)

These are some significant capabilities which should add value for most any environment.

If you purchase and enable Fragmentation Manager, here are the additional features that become available:

• Automated Defragmentation
  • Scheduled and manual rebuilds & reorgs, from the SQL Server instance down to partition level
  • Support for multiple concurrent defrag operations, which can dramatically reduce overall defrag time
  • Adjustable rebuild/reorg thresholds, scan mode and many other options 
  • Post-defrag analysis capability, so you can instantly see gains from defrag
• Calendar display of all historical and future defrag events, with drag-and-drop support
• Alerting for defrag success, failure, and completion
• Partitioning sliding window support for defrag (defrag only max partition, or all others)
• Historical trending via multiple charts on the Indexes tab:
  • Total server fragmentation, by fragmented % range
  • Total server disk space used, and wasted by fragmentation
  • Total server buffer space used, and wasted by fragmentation
  • Index fragmentation
  • Index disk/buffer space used/wasted
  • Index activity

In a nutshell, the base v7 software provides dramatically increased visibility into table/index size and fragmentation information, and Fragmentation Manager gets you automated defragmentation and many associated options, as well as historical trending.

FREE Licenses of Performance Advisor and Fragmentation Manager!

If the above included features weren't enough, we've introduced 2 new ways for you to get FREE software licenses:

1. If you own five (5) licenses of Performance Advisor and five (5) licenses of Event Manager, you will automatically get:
   • One (1) Performance Advisor license, for monitoring the SQL Server instance where your SQLSentry database is located ($1495 value)
   • One (1) Fragmentation Manager license, for defragmenting the SQL Server instance where your SQLSentry database is located ($795 value)
2. Regardless of the number of licenses you own, if you monitor the SQL Server instance where your SQLSentry database is located with a paid license of Performance Advisor, you will automatically get:
   • One (1) Fragmentation Manager license, for defragmenting this instance only ($795 value)

Some important points:

• These free licenses won't show up in your license counts, they are just "there" ;-)
• If you meet the criteria for #1 above and are already monitoring the SQL Server instance holding your SQLSentry database with a paid Performance Advisor license, then you're effectively getting a 6th PA license for free, which you can use to monitor any server.
• There are no ASM (annual software maintenance) costs for these free licenses, and they are perpetual.

Thanks again to all who participated in the beta, and we hope you like what you see in SQL Sentry v7!

Download SQL Sentry v7 here: New Users | Existing Users

SQL Sentry v7 Beta: First Look

It's been a while since my last post. Yes, we're still here (as you well know if you follow us on Facebook or Twitter), we've just been heads down since the PASS Summit getting v7 ready to ship. It's been a long road, but we're releasing the public beta today!

v7 represents the culmination of almost a year of effort, and ideas going back much, much further than that. We've completely redone several aspects of the software such as alerting (condition and action) configuration, and we've added some awesome new features like automated defrag, computer groups, and CMS support to boot. Did I mention SQL Server 2012? ;-)

Terminology Changes

We've made some long overdue changes to the SQL Sentry lexicon in the interest of making things clearer, and since I'll be using these new terms I wanted to get this out of the way first:

• A Device is now a Computer (pretty sure I just heard a collective HOORAY! – trust me, we had our reasons for devices, but we won't get into that here ;-)
• The former Global node is now the Shared Groups node
• The SQL Sentry Console is now the SQL Sentry Client
• The SQL Sentry Server Service is now the SQL Sentry Monitoring Service

Computer Groups

The first thing existing users will notice when they open the client is the new Shared Groups node at the very top of the Navigator. This node represents your entire SQL Sentry environment organized by Site. It is called "shared" because every SQL Sentry user sees exactly the same view here. The user-specific device registrations and groups (formerly Global) has been moved and renamed to Local Groups to better reflect what they actually are. You can still configure server-specific settings and below here, but not global settings – those are now set at the Shared Groups root node only.

Sites have always been there to enable logical partitioning of servers and monitoring services. For example, if your HQ is in Atlanta, but you have 100 SQL Servers in Miami and 200 SQL Servers in New York, you might install one monitoring service in Miami, and two in New York. You would create a site for each location, and place the monitoring services in the appropriate site so that they only monitor the servers in their location.

In v7, you can now easily apply special alerting rules to the servers in Miami and New York, versus having to touch each server in order to override global alerting settings:

In addition, you can create unlimited nested child groups in each site, and – you guessed it – apply specialized rules to those groups as well. The inheritance works exactly as it always has in SQL Sentry, you start at the highest level (Shared Groups), then override those global settings as needed at lower levels. Previously alerting & setting configuration looked like this:

• Global
  • Computer
    • SQL Server
      • Object (job, report, etc.)

Now it looks like this:

• Shared Groups
  • Site
    • [Child Group] [,...n] 
      • Computer
        • SQL Server
          • Object

As you can see, the ability to group servers can dramatically reduce the alerting configuration required for many environments.

Custom Object Groups

Being able to click on a group node in the Navigator and easily change settings for a bunch of servers at once is great, but it's inherently limited by the fact that a computer node can only exist in one group at a time in the navigator. What if you want to have another set of rules for servers that effectively "cuts across" navigator groups? For example, "All QA Servers" in both Miami and New York?

This is easy to do with custom groups. You simply create a new group by double-clicking the Object Groups node in the navigator, add the QA servers to it, then adjust the settings:

Similarly, if you wanted to disable Runtime Threshold alerts for all transaction log backup jobs, you can easily search on the jobs using a name pattern, use Shift + left click to highlight and add several at once, add the Runtime Max condition, then select "Disable".

Automated Defragmentation

Your first thought here may be, "I already have scripts that perform automated defragmentation, why do I need a tool?" Good question! Here are three compelling reasons:

• Manageability
• Visibility
• Defrag Speed

Manageability

There are several great scripts out there that many use to perform automated defrag. They can get the job done, but the main issue is that they are all, well, scripts. Configuring exactly which databases and indexes are defragmented and when can be a challenging and time-consuming task, especially if you are talking about 10s or 100s of SQL Servers. Manual script changes and multiple jobs on each server are typically required.

With our new Fragmentation Manager module, just like everything else in SQL Sentry, you can start at the top and work your way down. For example, if you have 20 SQL Servers, you can set a default global defrag schedule of 2am for all servers at once by enabling it at the Shared Groups level:

So in 30 seconds or less, you've configured enterprise-wide defrag!

DISCLAIMER: I am NOT recommending that you do this, as every environment is different, and you'd of course want to disable any existing defrag jobs first. I'm just letting you know what is possible ;-)

Typically you'll want to enable Fragmentation Manager at the SQL Server instance level by right-clicking the instance in the navigator pane, or clicking the Enable button on the new Indexes tab inside Performance Advisor.

Once you've enabled one or more defrag schedules, if you view the "Defragmentation Schedule" sample event view, or the calendars for any of those servers, you'll see defrag instances show up alongside other events:

You can of course drag-and-drop to move them. But what if you have a 100GB index on one of the servers that really needs to be analyzed and defragged separately? You simply select the index and override the inherited schedule:

It's that easy. Everything is point-and-click, and since the SQL Sentry monitoring service manages all of the defrag tasks, there are no scripts or jobs required.

Visibility

Once you've enabled the Fragmentation Management Module on a SQL Server, you'll see a new Fragmentation tab appear inside Performance Advisor:

This tab has tons of good information about your indexes, including 6 charts showing disk and buffer space, used and wasted, both total and at the index level. The purpose of this tab is not only let you know the state of fragmentation on a server, but help you make good decisions about how and when to defrag your indexes, adjust fill factors, or even change index definitions. One of the coolest charts on this tab is Index Space Usage (center bottom) – it shows you exactly how much of an index is on disk and in buffer over time, and how much disk and buffer space is wasted due to non-full pages.

There are also 3 new alerting conditions: Defrag Started, Completed, and Failure, so you can be as informed as you want to be regarding the status of your SQL Sentry defrag operations.

Speed

No, we haven't invented some magical new higher performance technology for analyzing and defragging your indexes... however, we have come up with a unique approach for potentially dramatically speeding up your regular defrag process, thereby reducing the maintenance window required for defrag – by allowing more than one concurrent defrag operation:

If your disk system can handle it, why not run multiple analysis or defrag tasks in tandem? Most systems we've tested have no problem running 2 or 3 concurrent defrag ops, especially when indexes are split across multiple data files and disks. An op can be an analysis, reorg, or rebuild. Currently this setting is capped at 5 for safety. I recommend starting with 2 concurrent ops on a test server, and see how it performs. With the Performance Advisor dashboard and Disk Activity views, you can easily assess the performance impact of increasing the concurrent defrag ops.

Alerting Enhancements

In addition to group-based alerting configuration, many other major improvements have been made in the area of alerting:

• You can now configure multiple actions of the same type for the same condition! For example, you can have 3 different Send Email actions for the Job Failure condition, each with different alert targets (users or groups), different rulesets, and different alert windows.
• What's this, "windows"? Yes, that's right, you can now set exactly when contacts should be alerted using configurable ranges of time, for example "Business Hours" or "Weekends". You can even create compound windows which combine multiple windows together.
• We no longer list all conditions by default, only those that are in effect. This can dramatically reduce the noise when viewing and configuring alerts.
• Inherited conditions/actions are displayed in one pane, and conditions/actions set at the current level are in another (Explicit).
• Since there can now be multiple levels of inheritance with groups, we show you exactly where the inherited settings are coming from via the Object column.
• You can choose to Disable, Override, or Combine with an inherited condition action. Combine works just as it sounds – you can set the same action again at the current level, but leave the inherited action in effect.

Together, I think you'll find that these changes make for the most flexible and robust alerting system we've ever had.

Performance Advisor Dashboard Enhancements

Aside from various cosmetic improvements, the two primary new features on the dashboard are NUMA support and mirroring queue monitoring. When monitoring a NUMA system, you'll notice that both the Windows and SQL Server memory charts are now split to show exactly how much memory is allocated to and used by each NUMA node. In addition, page life expectancy history is also shown for each node. When monitoring a system acting as a primary, mirror, or both, the Send and/or Redo Queues are shown on the same chart previously used to show backup/restore activity.

Beta Download

For a full list of all changes in v7 click here. I've really only scratched the surface. Please take the beta for a spin, and let us know what you think – we want your feedback!

New Users
Existing Users

As always, upgrading your existing SQL Sentry environment to the beta, and from the beta to v7 RTM is fully supported. Be sure to take a backup of your current SQL Sentry database first. Rolling back for any reason is easy – uninstall the beta, restore the database backup, then reinstall the previous version and point it to the database. No settings will be lost.

SQL Sentry v6.2: SharePoint Timer Jobs, Oh My!

We recently released SQL Sentry v6.2, and it introduces an exciting new product: Event Manager for SharePoint. v6.2 contains several other new features as well, including SSAS usage metrics, performance dashboard event overlay, enhanced console security, and a streamlined setup process. SharePoint support and SSAS usage in particular have been heavily requested, so this release should make quite a few customers very happy!

SharePoint Timer Job Monitoring

If you already use SharePoint, you may be familiar with the concept of timer jobs. SharePoint 2010 has its own scheduler service that carries out various background tasks on an ongoing basis – over 100 timer jobs out of the box, ranging from health checks to data imports to history cleanup. These jobs can run on any server in the farm, and they can access resources on other servers. Most of the jobs are set to run on predefined recurring schedules, and the net result is a significant amount of timer job activity going on pretty much all day and night. Since much of this activity is touching SQL Server, it's critical for the database administrator to have visibility into exactly when the jobs are running, and to be able to ascertain the impact they are having on performance.

Using the Event Manager for SharePoint calendar, here's a shot of a typical SharePoint timer job schedule over two days:

The instances in orange indicate a runtime overlap of at least 20 seconds, so it's apparent that many timer jobs run at the same time – that's right, the dreaded schedule collisions! One of the primary reasons for the existence of the Event Manager calendar is of course to be able to detect and resolve schedule collisions, so even if you've already cleaned up your SQL Agent schedules, you'll be able to put it to good use again for timer jobs ;-)

Here's a shot of the 12am slot with timer jobs only:

We have no less than 20 timer jobs running concurrently at midnight every night. The calendar view can be easily expanded to include other events to see if these jobs are colliding with SQL Agent jobs, for example.

Here's a closer view of the 4pm slot, with SQL events from the timer jobs included. You can distinguish the timer jobs from the SQL events at a glance by the small glyphs in the upper left of each instance:

I know, this is pretty ugly, so I'll apply a quick filter to show only events with duration >3 seconds:

Again, these are all out of the box jobs and schedules! In multi-server farms, not all jobs may be running on the same server, so the server on which a job ran is shown in its tooltip along with many other details.

Timer job schedules can work quite differently than SQL Agent, Windows Scheduler, or other schedulers you may have seen. They can use the concept of "ranges" – you set possible start and end times, and the job can run any time during that range. Supposedly the randomness will help avoid job collisions... but note the "SharePoint BI Maintenance" job in the middle of this shot with odd start time of "12:00:40 PM":

Yep, you guessed it, it's using a range! If I right-click the job and select Properties, I'm launched into the job schedule in the SharePoint Central Administration web interface:

We can see that this job is set to run hourly, at any time during the hour. So the randomness didn't help much here – it ended up running concurrently with 4 other jobs. Personally, I like to control exactly when my jobs run and not to leave that up to chance, so I changed both range values to 30, which effectively tells the job to run only at 30 minutes past the hour. (NOTE: You need to be setup as a farm administrator to access the schedule properties.)

Can timer jobs impact SQL Server performance? You'd better believe it! I'll demonstrate using another new feature, Dashboard Event Overlay (more on this below). By right-clicking a longer running timer job and selecting Jump To > Performance Advisor > Dashboard...

...I'm taken right into the Performance Advisor dashboard where the event duration is overlaid on the X-axis of each chart:

We can see that there is a strong correlation with this job and several metrics, including:

• Network inbound
• CPU
• Windows hard faults
• Disk latency
• Transactions and batches
• Bookmark lookups
• Disk waits
• Log flushes

Since this particular job runs every 30 minutes, the real question becomes, how will it impact user activity against SharePoint databases on the same server throughout the day? More on this in a future post.

SSAS Usage Totals

The next major new feature is the SSAS Usage Totals tab, which is part of Performance Advisor for SSAS:

Previously, SSAS usage information was only available at the query level, and you were unable to see the actual attribute members requested. This made it difficult to get a server-wide perspective on data access patterns. You can now see this server-level data aggregated three ways – by attribute, aggregation, or partition – over any date range. If you spot a particular attribute combination that is heavily requested by many queries, you can right-click the row and copy the attribute vector to the clipboard, then paste it into Agg Manager to create the aggregation. (Look for this capability to be fully integrated in a future release.)

Enhanced Console Security

A common need, especially in larger, more segmented environments, is the ability to restrict visibility and access to certain servers to specific users. Previously, in the SQL Sentry Console you could use groups to effectively hide servers from unauthorized users, and we've always leveraged native security to ensure that there are no escalation of privileges risks – just because someone could "see" a server didn't mean they could do anything with it. Although this was fine for many environments, in others, unauthorized users aren't even allowed to know that certain servers exist! This was a problem.

Hence the need for this new capability, which allows a SQL Sentry administrator to truly restrict SQL Sentry users to specific SQL Servers via their Windows login:

Once associated, you simply select the servers that the user can access:

For existing environments, nothing changes automatically when upgrading to v6.2. However, once a user has been associated with at least one SQL Server, from that point forward they can no longer access any other SQL Servers monitored by SQL Sentry in any way. This goes for any new servers that may be added later – the user must be explicitly associated with those servers before they are accessible via the console.

Dashboard Event Overlay

A powerful new feature is the ability to overlay events across the Performance Advisor dashboard charts (see screenshots above). Previously, you might know that a SQL statement ran between X time and Y time, and you suspected that it was impacting performance, but when you flipped over to the dashboard there were no visual cues as to exactly when the event ran – you had to perform the correlation in your head. Now, if you use the Jump to Dashboard context menu from either the Event Manager calendar or the Top SQL tab, you'll notice new horizontal indicators on the X-axes representing the selected event(s). When coming from the calendar, any event highlighted using the new enhanced highlighting options will have a corresponding indicator on the dashboard. This means that you can easily see what impact a particular user, application, or even SPID, is having on performance!

Streamlined Setup

Last but not least, we took a hard look at the installation process and initial setup wizard. Several screens were eliminated and/or consolidated, and the process was simplified overall. In addition, we've added a new Start Page which gives you quick access to some of the more commonly used functions, every time you launch the console:

We'll be adding more to this page as time goes on.

Take v6.2 for a spin and let us know what you think: new users | current users. I'm at the PASS Summit this week, so stop by for an in person demonstration, as well as a sneak peak at some exciting new features coming in v6.5!