Review: Performance Advisor for SQL Server 5.5

Given how popular performance monitoring solutions are among SQL Server solution vendors, I was excited to look into SQL Sentry’s offering in this category. Performance Advisor for SQL Server 5.5 is an agentless monitoring solution for SQL Server 2000 and later (although the monitoring server needs to be on SQL Server 2005 or later). I had heard a lot of great comments about this solution from clients and peers. What I found is that Performance Advisor lives up to this reputation.

The installation was painless and didn’t require any effort on my end, except for having to reboot after Microsoft .NET Framework 4.0 was installed. However, I was disappointed that one of the installer’s screens recommended using domain administrator credentials to ensure enough privilege for Performance Advisor to remotely monitor servers. Granted, the kinds of low-level diagnostics that performance monitoring entails simply can’t be accomplished without elevated permissions, but my policy is that any solution that recommends running services or applications with domain administrator credentials loses an entire diamond. However, I deducted only half a diamond because the Quick Start guide that accompanies the SQL Sentry installer provides some of the best instructions that I’ve ever seen on how to configure least-privilege performance monitoring. As such, SQL Sentry has done due diligence in providing best-practices support and guidance, but that guidance is missing from the installer.

After installation was complete, I went about setting up servers within my lab to monitor. Given that Performance Advisor touts agentless monitoring, I was anticipating a fairly easy deployment process. What I got, however, actually blew me away in terms of ease. All I had to do was work through a very simple wizard to add servers to the main SQL Sentry console, then just right-click the servers that I wanted to watch and within two mouse clicks the servers were configured for monitoring.

When I looked for my remote servers on the dashboard, I was greeted by screens with ugly red text reporting remote procedure call (RPC) errors. I quickly determined that these errors were due to firewall problems (the servers in my lab run default installations of Windows Firewall) and found a long list of needed ports buried within the appendix of the Quick Start guide. Given how smooth the rest of the installation and configuration process went, encountering these errors was disappointing. Most end users probably wouldn’t immediately link RPC errors to potential firewall problems, so the Quick Start guide or the requirements documentation should have mentioned the potential need to open firewall ports. Alternatively, the RPC errors in the dashboard could be coupled with a link to instructions on how to punch holes in a firewall.

After I solved that configuration problem, I analyzed my servers’ workloads, looking for potential performance culprits, bottlenecks, or other problems. Besides being able to distill and weight performance metrics, you can move seamlessly between historical and current performance metrics. Being able to contrast current conditions against historical trends makes it much easier to quickly spot potential problems or bottlenecks. This ability can be especially helpful for less experienced DBAs because it’s all too easy to get distracted by individual counters or red herrings that might not be problems at all.

What makes Performance Advisor really shine, though, is the ease with which you can set up alerts for various SQL Server performance conditions. Doing this on your own isn’t rocket science, but it can be time consuming to set up alerts from different monitoring tools and resources. Performance Advisor rolls all these tools and resources into a set of unified action panes, which you access using tabs.

Performance Advisor is a solid product with great features and capabilities. Although I ran into two problems during evaluation, they were easy to address and didn’t affect the solution’s overall useability.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More