In-Depth

Keep Tabs on SQL Server

These two SQL Server monitoring tools go head-to-head.

• By Josh Jones
• 01/01/2007

In any given environment, there are more servers than administrators. To keep an eye on all your servers and ensure they're online and operating at peak efficiency, you need tools that not only tell you when the servers are down, but also alert you when a runaway query is about to cause your production cluster to melt down.

There are several tools specifically designed to help with these issues. Here we'll examine how two of the more popular tools stack up against each other: SQL Diagnostic Manager from Idera and Spotlight on SQL Server Enterprise from Quest Software.

SQL Diagnostic Manager
Idera
SQL Diagnostic Manager serves three basic functions: real-time performance monitoring, alerts and historical trending. And it accomplishes all this without installing a single agent or object on the servers you're monitoring.

Diagnostic Manager presents you with a dashboard-style interface broken up into three main panes for real-time monitoring. In the primary pane, there's a collection of "monitors" (icons that actually look like CRTs) displaying current performance metrics such as CPU utilization, disk I/O, current number of batches and current number of processes for each server. In the same monitors, there are also icons showing the status of the SQL Server service, the SQL Agents and the alerting status of other ancillary features like Full-Text Search.

Below that pane, there's an event log-style list of "To Do's." Many of these items are simply informational, such as letting you know the SQL Server Agent Service has started. However, whenever it's relevant, double-clicking on these line items launches additional windows with in-depth statistics on that entry. For example, double-clicking on the entry "12 tables have a reorganization percentage less than the specified threshold," opens a new window with a graphical breakdown of the index fragmentation for each of those 12 tables.

On the left side of the original console is a third pane with a tree view similar to Enterprise Manager. There are a number of child nodes below each server node that let you see all the statistics being collected by Diagnostic Manager. One notable feature of this view is the "All Servers" node that shows aggregated views of data from all of the monitored servers. This can be very handy when looking for issues that span across multiple servers.

[Click on image for larger view.]

Figure 1. Idera's SQL Diagnostic Manager gives you extensive details on all aspects of your database files.

Threshold-based alerting is Diagnostic Manager's next major function. You can send alerts to multiple destinations, including e-mail, event logs, ODBC connections, pagers and NET SEND messages. You can also configure alerts to launch other external programs when triggered. You can configure multiple types of alerts for each metric, and establish warning and error thresholds for each. This gives you a high level of customization in terms of alerts.

For example, you could configure Diagnostic Manager to send a warning to an ODBC connection that connects to a ticketing system when any given database reaches 75 percent full. If the database reaches 90 percent full, though, you could have the system trigger an error, then page the on-call DBA.

Diagnostic Manager tracks most of the major operating system metrics, like percentage of CPU usage. It also collects nearly every major SQL Server metric, such as Database Size, Log Size and SQL Memory Usage.

[Click on image for larger view.]

Figure 2. In Idera's SQL Diagnostic Manager, you can drill down and select the specific characteristics you want to view in any window.

Due to the nature of its primary functionality, Diagnostic Manager provides a wealth of historical information you can use to trend everything from poor performing queries to database size. It's important to note, however, that the metrics repository isn't installed as part of the base Diagnostic Manager. It's a separate process that installs to a specified server, as well as a Windows service or sample reports. Diagnostic Manager stores its real-time data in a proprietary format.

The Metrics Repository Manager lets you configure a schedule that governs how frequently monitoring data and the metrics database will be synchronized. You can retrieve the data from the Metrics Repository with a set of documented stored procedures. While the sample reports use Reporting Services, any reporting tool can use the stored procedures to retrieve metrics data.

There's also a Web Console that is actually a browser-based implementation of the Diagnostic Manager interface. Installing the Web Console simply requires IIS on the host server. It then creates a virtual directory under the default Web site of the host server, so be sure to install this on a server where it won't be a problem. Once installed, this tool makes a great interface for junior level administrators or NOC personnel to monitor SQL Server performance without granting them access to the SQL Servers themselves.

[Click on image for larger view.]

Figure 3. The main dashboard in Idera's SQL Diagnostic Manager shows you a multi-pane view, including a CRT-style display of performance metrics.

The tool supports all editions of SQL Server 2000 and SQL Server 2005 for both the central management server as well as monitored servers. However, as with all third party tools that connect to SQL Server, monitoring with Diagnostic Manager means the overhead of additional queries being run against the server on a regular basis. To collect more advanced statistics like index fragmentation, Diagnostic Manager has to run server-side traces as well as various DBCC commands. Be sure to evaluate that overhead to be sure this won't create performance problems.

Diagnostic Manager does what you need a real-time performance-monitoring tool to do, plus it has additional alerting and historical trending features. Idera is now finishing work on the next version of Diagnostic Manager, which will include expanded performance monitoring in terms of operating system metrics as well as query performance.

Spotlight on SQL Server Enterprise
Quest Software
Spotlight on SQL Server Enterprise gives you a comprehensive view of current performance trends for all SQL Servers in your environment, also with a dashboard-style interface. You can also configure threshold-based alerting for a variety of conditions.

To set up Spotlight, you'll have to install a "work" database on each monitored server. This is in addition to a central middle-tier server (appropriately named the Diagnostic Server) that serves as the communication relay between the monitored servers and the Spotlight client application. This server also collects data written to the repository (whether or not any client application is open), letting you review past performance information. The Spotlight repository is a collection of flat files stored on the Diagnostic Server -- it is not a relational database.

Once you've installed all the components, you'll use the Spotlight client application to create and store connections to the monitored SQL Servers. Once you've created these, you're ready to roll with Spotlight.

The interface presents a near real-time dashboard that displays a server's various performance characteristics. For each of the statistics displayed, there's an animated icon showing a numeric value. The speed at which the animation moves changes based on the activity of its associated metric.

For example, the Paging counter shows a circular set of arrows that speed up and slow down (as well as change color) based on how much paging is happening at the moment. Drilling into any of these metrics shows you additional graphs with more detailed information and related statistics. You can further drill into each of these graphs to reveal more and more detailed information. There's also a navigation tree in the left hand pane that lets you quickly return to the summary view, as well as displaying nodes for each monitored server.

[Click on image for larger view.]

Figure 4. The dashboard view in Quest Software's Spotlight on SQL Server Enterprise lets you configure threshold-based alerts for many factors affecting database performance.

You can configure thresholds for each metric. This lets you customize the look of the individual statistics based on specific values. For example, you can set up the Locks threshold and have it change the display from green (meaning optimal performance) to yellow (warning) at a specific value. This way, you can quickly identify potential locking problems on the server as soon as they begin to appear -- well before they reach a critical threshold.

You can configure e-mail alerts for each metric as well, also set for various threshold levels. These configuration options give you the ability to create tiered alerting schemes, using visual cues on low-level alarms for on-site staff to address. E-mail or paged alerts go directly to higher-level support personnel when critical thresholds are surpassed.

Spotlight gives you a historical view of the past performance of all your monitored servers. You can retrieve the past seven days worth of information for each server, and "replay" the history to review how the performance characteristics changed over time. This can be valuable when you're trying to relate user reports to performance data for troubleshooting.

Unfortunately, the seven-day limit for historical data is limited to 15GB. In busy environments, 15GB may not actually store seven days worth of data. As they say, your actual mileage may vary.

You can also use Spotlight to view and change various SQL Server configuration values, such as the affinity mask or the default fill factor for indexes. Besides being able to change these values, Spotlight gives you more detail on each option, including recommended settings for some of the higher-level options.

Besides the configuration options, Spotlight gives you the status and history of the various support services used by SQL Server, such as the SQL Server service itself, the SQL Agent and Cluster Services. It also collects data on SQL Agent job history, SQL Agent alerts and log shipping. Centralizing this information helps minimize the time you have to spend switching between applications to monitor and manage SQL Servers.

[Click on image for larger view.]

Figure 5. The animated monitoring view in Quest Software's Spotlight on SQL Server gives you live feedback on SQL sessions, memory usage and disk space.

One last feature of Spotlight gives you a choice of multiple monitoring and management strategies. When you install Spotlight, it creates two new Windows groups on the Diagnostic Server called the "Quest Diagnostic Administrators" group and the "Quest Diagnostic Users" group.

You can view and change options within the Spotlight tool as well as on the monitored SQL Servers. This is especially useful in shops that have Network Operations Center technicians who monitor server performance 24/7 and higher-level administrators who manage those servers and handle escalated issues.

You can add NOC personnel to the "Users" group so they can monitor performance and do initial troubleshooting and data gathering. Similarly, you can add the server administrators to the "Administrators" group to manage both the Spotlight tool and the SQL Servers themselves.

While Spotlight can monitor multiple instances of SQL Server, the documentation recommends no more than 50 instances be monitored by one Diagnostic Server (for performance reasons). This is an unfortunate drawback. For environments with hundreds of SQL Servers, you'll need to maintain multiple Diagnostic Servers. Be sure the user workstations have enough resources to run the client without bogging down the system.

Overall, Spotlight on SQL Server Enterprise is a comprehensive tool for real-time monitoring and immediate responding to adverse performance conditions. Multiple instances of the client running on different workstations will all see the same data (provided they are configured to use the same Diagnostic Server). This will help ensure consistency across teams of people who may be trying to troubleshoot the same problem.

The Final Word
While both of these tools satisfy most of your SQL Server monitoring needs, their respective features don't overlap 100 percent. Diagnostic Manager's dashboard interface shows you a very basic view of all of the servers in your environment. Spotlight on SQL Server's dashboard gives much more detail for each server in its high-level view, though those details come at a cost.

During testing, the Spotlight client interface took nearly two times more CPU and RAM resources than Diagnostic Manager. Where historical data is concerned, however, Diagnostic Manager provides a much richer feature set. With Idera's tool, you can review days, months or even years worth of data to analyze trends and predict future needs.

Both tools provide alerting capabilities, but Diagnostic Manager gives you greater flexibility for alert destination options. You can send automated alerts to e-mail, ODBC, and application log entries, as well as automating initial responses to those alerts. Spotlight's alerting is fine if all you need are e-mails and visual cues.

Each tool lets you respond to alarm conditions manually. Whether you need to kill a process, restart an SQL Agent job, or perform a re-index, both tools have you covered. Spotlight provides more management capabilities, like letting you reconfigure SQL Server options from the client interface. If you want to cut down the number of applications you use for your day-to-day work, Spotlight could be one step towards that goal.

In terms of detailed metrics, both tools do a great job of exposing information on all levels of performance and troubleshooting. They both monitor and alert on everything from hardware resources to SQL batch statistics and index usage. Either tool will shed light on your SQL Server performance issues. Deciding which of these tools is better truly depends on your needs and the factors outlined here.