SQL Sentry v5.5 Need to Know

SQL Sentry v5.5 Release Candidate 2 was just published to the website. Although it’s a dot release, don’t be fooled, v5.5 stands to be a watershed release for us in more ways than one. Not only does it represent the culmination of many months of hard work by the team, but it contains what have easily been the most heavily requested enterprise features since the original release of Performance Advisor for SQL Server in 2008:  the Global Server Status View and Performance Reporting. There are also many very cool secondary features such as Automatic Disk Misalignment Detection, Mount Point Support, as well as a variety of performance optimizations.  Read on for more details.

Global Server Status View

Finally, you can view key performance metrics for more than one server at a time from the SQL Sentry Console! You simply right-click the Devices root node, or any device group node, and select the “Open Performance Advisor Overview” context item:

There are a couple of different ways to look at the data. The default is Windows metrics at the top level:

From there you drill in to see metrics for each SQL Server or SSAS instance on the server. Just like on the PA dashboard, we also show how much Windows resources are being consumed by the SQL Server and SSAS processes for network, CPU, and memory:

The level below that shows the actual instance metrics, which will of course change depending on whether you’re looking at SQL Server or SSAS.

If you only want to see SQL Server and/or SSAS metrics without the Windows, simply uncheck Windows in the dropdown at top. This gives you a very clean look across all SQL/SSAS instances in your environment:

As shown in the shots above, if a performance metric exceeds commonly accepted preset thresholds (such as Page life expectancy < 300 secs), the cell and associated server name will turn red to highlight it. And to answer the question you are about to ask… no, there is currently no way to customize this, but this is coming. 😉 For this release the primary focus was getting the alarming framework in place with some basic presets.

However, to hold you over until, you can set custom filters for any column or combination of columns. For example, if you want to see only servers with “Transactions/sec > 100 OR Blocks > 0 OR Deadlocks > 0”, simply click the column header and set a filter:

As the view auto-refreshes and metrics change you’ll see server rows pop in and out. This way you can stay focused on the servers requiring your immediate attention, based on limits you’ve defined. Here’s what a filter looks like once set:

Note there are now only two visible servers, the rest are hidden by the filter.

When you combine this with the ability to adjust the interval displayed, filtering becomes an even more powerful feature:

Let me elaborate.  By default the grid shows the last raw sample value collected, but since many metrics are sampled every 10 seconds there may be temporary spikes that you want to ignore. You may not want the filtered server list changing every refresh.  If you simply increase the interval to anything other than “Last Sample” — say “10 Minutes” — the metrics displayed will be last calculated average over that interval. This can effectively smooth out many spikes, and thus prevent a lot of volatility with the server list.

Even without the use of filters, this is a great feature for getting quick snapshot of how all servers have been performing over different ranges, up to the past 3 days.

One last item on the global view — if you don’t care for the default dark skin, you can change to a standard light grid color using a new user preference:

Performance Reporting

There has always been an enormous amount of valuable performance data contained in the SQLSentry database, but aside from the PA dashboard there really wasn’t an easy way to get at it. With the new SSRS-based performance reporting, now there is! To see all of the new reports, select “Reports –> Performance Advisor” from the menu bar:

Here are a couple of quick shots of the “Waits Analysis” and “File Utilization by Database” reports:

Not only do we have a great list of the most commonly requested reports, but we have two ways to generate completely customized reports – the Performance Counter History report, which lets you select any combination of servers, counters and instances:

…as well as the Performance Counter Date Range Comparison report, which lets you do pretty much the same thing, but using two different date ranges.

One of the coolest aspects of our custom reports is that you’re not limited to only standard Windows perf counters – you can report on any of the performance data we collect, including data from DMV’s, WMI, and even internally calculated metrics like Waits by Category! And just like the PA dashboard, you can select any date range, from 3 minutes to 3 years… and it won’t kill your server in the process. A lot of effort has been put into ensuring that the reports generate quickly and will minimally impact the performance of the SQLSentry database.

You can save any of the custom reports by clicking “Save” on the parameter selection window, as well as deploy the reports to any SSRS instance using the “Deploy Reports” menu item.

Disk Activity View Enhancements

We’ve introduced two significant enhancements to our patent-pending Disk Activity view:  Auto-Misalignment Detection and Mount Point support. Which you will like best will depend a lot on your environment.

Automatic Disk Misalignment Detection

As you probably know, misalignment is no longer an issue with new Windows Server 2008 installs since it uses a default offset of 1024KB. However, what we have found is that many servers out there are either still running Windows Server 2003, or were upgraded from Server 2003 to 2008. As a result this is still a big issue in many environments, and we’ve just made it dramatically easier to detect – you simply watch a server with PA, then look at the Disk Activity screen. If the logical disk has a red background, it means the offset is suspect:

PA shows you exactly what the offset is in the tooltip for the logical disk. I wish I could say we will then auto-fix it for you, but you probably realize that would not be possible. 😉 However, on this same screen you can clearly see whether or not latency is actually a problem on the misaligned disk, and from there determine whether or not you should actually go through the process of moving database files and rebuilding the partition with a proper offset.  Here’s another server where all partitions are misaligned, and where the disks are experiencing pretty severe latency:

BTW, if you haven’t seen this screen before, all of those color-coded bars are database files — data files and transaction logs. Same color means same database, diagonal hash marks mean transaction log. Bottom connector pipe for each disk is writes, top pipe is reads. Wide red fill in the pipes means high latency, thin green means IO activity but acceptable latency.

For more details on the performance implications of disk misalignment and what you can do about it, check out Microsoft’s Disk Partition Alignment Best Practices for SQL Server whitepaper.

Mount Point Support

Another common request that we’ve been getting more and more over the past year or so is for mount point support, in large part because of the explosion of VMs and associated LUNs in the environment. Now when you look at the Disk Activity screen, you’ll see mount points displayed alongside all other disks:

PA shows the same latency and volume information for mount points and database files on mount points as it always has for other disk types.

Technically these disk-related changes were no small feat to pull off, but once again the dev team rose to the occasion!

Performance Optimizations

We are always looking for new ways to increase the performance of SQL Sentry and minimize our footprint in the environment… a footprint, I might add, that is already dramatically lower than any competitive product. (We will actually be proving this very soon… stay tuned.) However, in this release we’ve made some changes that would be considered significant, even by us.

Redesigned Threading Model

Two changes have been made to the threading model. Previously, dedicated threads were spawned for each monitored server, and each would consume memory, so the more servers you were monitoring, the more memory would be used by the SQL Sentry Server service process (you need one of these services for approximately every 100 or so monitored servers). A new thread pooling system is now in place that eliminates most of these dedicated threads, dramatically reducing the process’ memory footprint and increasing scalability at the same time.

Next, for some of the collection subsystems we’ve moved from a fork/join threading model to more of an asynchronous work queue model. Fork/join threading is similar to parallelism with SQL Server. There are some benefits to this approach, but a primary drawback is that the process can only be as fast as the slowest thread. The new model effectively eliminates the possibility of collection delays caused by offline servers, network issues, etc., that we’ve sometimes seen affect other critical SQL Sentry processes like chaining and alerting.

Reduced Plan Cache Footprint

Prior to v5.5, ad hoc SQL was being used in some places by both the SQL Sentry Console and Server, and in certain scenarios over time this could lead to plan cache bloat and some buffer pressure on the server where the SQLSentry database was installed. In v5.5 we’ve eliminated almost all uses of ad hoc SQL, which dramatically reduces the plan cache footprint of the SQLSentry database. This will not impact monitored servers, only the server where the SQLSentry database resides (unless of course it is monitored as well. 😉 After you upgrade, I recommend that you run DBCC FREEPROCCACHE on that server so the buffer can immediately reclaim any of the space previously used for the ad hoc cache.

I really hope you enjoy using v5.5. New users can get bits here, existing customers please login to your portal account. As always, we fully support upgrading to this build from any earlier SQL Sentry version, and from this build to the final release of v5.5. Thanks to everyone who has shared their ideas with us, our great beta testers, and of course to the entire SQL Sentry team for your efforts in finally making these great new features a reality!

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.