Friday, October 26, 2012

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:

cpu_processes_sample

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

cpu_jumptoprocesses

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

processes_tab(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!):

memory_processes

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

cpu_numa_pgs(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!

eventlogs

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:

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:

condition_filter

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!

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!