SQL Server Performance Monitoring and Management Tools

Many factors affect the performance of your Microsoft SQL Server–based applications. When application performance begins to suffer, determining root causes can be challenging. This is when real-time performance monitoring and management tools can make a difference. I've reviewed two easy-to-implement solutions that will make your life easier and help you reach resolution faster when your SQL Server solutions go south: Idera's SQL diagnostic manager and Quest Software's Spotlight on SQL Server Enterprise. Both products monitor SQL Server and Windows OS metrics and generate alarms when predefined threshold values are crossed. Both display server status graphically and let you drill down into more detailed information. Both let you archive metrics to a SQL Server database and provide sample reports in SQL Server 2005 Reporting Services (SSRS) format.

I had intended to include BMC Performance Manager for Databases in this review. Unfortunately, after spending more than two days obtaining and attempting to figure out how to successfully install the product, I ran out of time. To be fair, I'm sure the suite of products that BMC sells is top-notch, once you get them installed and working. But the difficulty I experienced using the documentation to install the product forced me to eliminate it from consideration in this review.

SQL DIAGNOSTIC MANAGER

Idera's SQL diagnostic manager (SQLdm— I reviewed version 4.6.8) is an easy-to-use tool to manage and monitor multiple instances of SQL Server 2005/2000/7 in real time across an enterprise. SQLdm is a full 24 X 7 monitoring solution with drilldown capability for problem analysis and archiving for historical analysis. Whereas other companies offer performance-monitoring products to support different vendors' solutions, Idera focuses solely on products for SQL Server.

Architecture

SQLdm has a fairly simple design. Idera recommends that you install it on a server that doesn't host SQL Server. A SQLdm service queries the SQL servers that you tell it to monitor. SQLdm installs nothing on the monitored server—no agents, services, databases, or stored procedures. SQLdm uses native SQL Server monitoring interfaces—DMO (Distributed Management Objects), SMO (Server Management Objects), remote queries, and (when turned on) a remote lightweight trace—to obtain information from monitored servers. During quiet time that you configure, SQLdm queries for database growth and table reorganization information and also queries Performance Monitor counters for OS metrics. By default, data is stored in flat files on the SQLdm server for seven days. With the SQLdm Metrics Repository, you consolidate and move the data to a SQL Server database for historical analysis and after-the-fact problem determination. A suite of customizable predefined reports that use SSRS queries the Metrics Repository and lets you create a user-driven reporting system. Web Admin, an independent module that is part of the SQLdm package, remotely monitors SQL servers and offers a limited subset of the metrics and monitoring capabilities of SQLdm's server-based GUI.

Installation

SQLdmwould be a snap to install if Idera would make the installation guide a little more accessible and state system prerequisites a little more clearly. After I downloaded the installation executable, I wanted to download an installation guide. I looked around Idera's Web site and couldn't find one, so I simply forged ahead. As it turned out, the installation package presented an option to view a one-page installation summary as soon as it finished extracting files. SQLdm has two installable components: The first installs core data collection components and a GUI console. The second installs the Metrics Repository, which lets you archive data for reporting and trend analysis. Web Console is a third, independent component that monitors only SQL Server 2005 and SQL Server 2000 databases and exposes a subset of the metrics available in SQLdm.

Installation requirements are basic: a 1.1GHz Pentium or better processor, 384MB of RAM, Microsoft Data Access Components (MDAC) 2.8, Microsoft .NET Framework 1.1 SP1, SQL Client Tools, and Internet Explorer (IE). For some reason, the installation summary doesn't mention the need for SQL Client Tools, but the installation routine prompts you for it later if it's not installed. I installed Connectivity Components and Management Tools from the Client Components section of SQL Server 2005 Setup, and this made the primary SQLdm setup routine happy. As I discovered later when many of the metrics I expected weren't available, SQLdm requires the SQL Server 2000 Client Connectivity tools—the SQL Server 2005 version I installed wasn't sufficient.

The downloadable installation program presents a simple yet effective Web-based Getting Started interface. Installation of both SQLdm and the Metrics Repository took just a few minutes after I configured the server with the requisite software. Installing the Metrics Repository database required a target SQL Server instance for data storage. You use the SQLdm Metrics Repository Service Manager to configure how often to write monitored metrics to the repository. Both SQLdm and the Metrics Repository run as services. Web Console installs quickly on a system configured with Microsoft IIS and ASP.NET and doesn't need to be installed on the same system with SQLdm.

The GUI Console

SQLdm's GUI makes it easy to add monitored servers, view the current status of each, and drill down into the views for extended analysis. As Figure 1 shows, on the left side of the GUI you'll find a tree view of all monitored servers, along with an All Servers view. The All Servers view consolidates selected information across all monitored servers, allowing you to view and filter key kinds of information.

The Summary display panel of the GUI graphically displays the status of each monitored server. Icons display the status of key services, and small graphs show CPU utilization, user counts, I/O rates, and SQL batch execution over the past hour. Right-click menus offer access to a wealth of detailed metrics, and context-sensitive Help is available. For example, positioning the cursor over a point on a graph displays the metric for that area. Clicking in a server's graphical display brings up the Server Summary screen, which includes key metrics and the status of each database. The SQL Performance and OS Performance tabs in the Server Summary screen graphically display recent values for key metrics for the server. The Details view, an alternative to the Summary view, displays the status of monitored servers. Sites with large numbers of SQL servers will use the Custom View feature: By assigning each server a server group, application group, department, and location, you can create a variety of custom views that are easily selectable from a drop-down box.

To Do List is the third window in the GUI. Here SQLdm displays alerts when a monitored metric exceeds one of the predefined threshold values. Double-clicking an entry lets you drill down into additional information relating to the event—a particularly easy way to start digging for root causes. Clicking a check box marks an event complete.

After installing the three components of SQLdm, I used the primary GUI to begin monitoring several servers. Using either the wizard or the direct-entry methods was quick and easy, and in each case SQLdm immediately displayed graphical evidence of monitoring. Since SQLdm installs nothing on monitored servers, there was no delay in displaying results.

As I exercised the product further, I discovered that I needed to enable OLE Automation on monitored SQL Server 2005 instances in order to collect the metrics displayed on the OS Performance tab of the Server Overview screen. Although OLE Automation is something one normally enables by using the Surface Area Configuration for Features tool in SQL Server 2005, Idera made enabling OLE Automation a one-click procedure from the OS Performance tab.

Monitoring queries and stored procedures were the only other functions that I needed to enable for each monitored server. SQLdm reports the worst performing and the most frequently run stored procedures, triggers, single statement SQL queries, and SQL batch queries filtered by the query types and performance thresholds you configure.

An Idera representative told me that with full monitoring enabled, server overhead typically doesn't exceed the 2 to 4 percent range. Although I wasn't able to validate this claim, I saw nothing that would lead me to doubt it.

SQLdm will also collect table growth and fragmentation statistics during a daily two-hour quiet time window that you specify on selected days of the week, for selected databases.

The drill-down tools are a powerful way to not only see real-time metrics but also to select (by day and date range) and view historical raw metric data (with a default of one week) for after-the-fact analysis. SQLdm lets you export displayed graphs or data to the Clipboard or Excel with a mouse click.

SQLdm offers a flexible alerting system that is configurable at a granular level. For monitored metrics, you can set warning and error threshold levels for each monitored SQL instance. Notification destinations are configurable by SQL Server instance, metric, and severity level. SQLdm supports several notification destinations: to a list of email addresses, Windows application event logs, Windows messages, any ODBC data source, and an external script. For jobs, SQLdm lets you filter alerts by SQL job category and lets you configure alerts on abnormally ending jobs and on jobs that run a specified percentage of time longer than the average run time.

Web Console

The Web Console takes some configuring in the beginning: You must configure servers and server groups, and the accounts that will authenticate access to them. One limitation is that all users are equal. Although you can create server groups to make it easy to navigate to the servers you manage, all users have the same access to all servers. The interface is reminiscent of Outlook 2003, with three primary sections: Server Status, Agent Status, and Performance Analysis. The information presented is a small subset of what's available by using the GUI, and you are limited to viewing one server at a time—there is no "all servers" consolidation that the console GUI includes.

The lack of a way to install multiple consoles that connect to and share a single data collection service is a limitation that Idera plans to eliminate in the next SQLdm release, due out later this year.

SPOTLIGHT ON SQL SERVER ENTERPRISE

Quest Software's Spotlight on SQL Server Enterprise is another easy-to-install, quickly usable product for monitoring instances of SQL Server 2005 and 2000. Michael Campbell reviewed Spotlight on SQL Server 5.7 (not the Enterprise version) in February 2007 (InstantDoc ID 94548). I'll highlight the additional features of the Enterprise version and expand on the two products' common features.

Architecture

Key differences between the standard and Enterprise versions of Spotlight on SQL Server are architectural. In the standard edition, each Spotlight installation connects directly to the monitored SQL servers. When monitoring a SQL Server instance from several locations (i.e., from several installations of Spotlight), each location will connect to the monitored server independently. The Enterprise edition introduces a Diagnostic Server and an Enterprise Viewer. The Diagnostic Server connects to each monitored server and manages data collection. Multiple GUI consoles can connect to the Diagnostic Server, ensuring that each monitoring location has the same view of monitored SQL Server instances while eliminating the load that additional, redundant connections would impose on the monitored server. The Diagnostic Server runs as a service and incorporates its own database system for short-term data storage. When you create a connection to a SQL Server instance, Spotlight installs a database and stored procedures on that monitored instance that it uses for data collection. Spotlight on Windows Enterprise is included in both versions of Spotlight on SQL Server and adds the ability to monitor and archive Windows OS metrics. In the GUI you'll see two connection icons for each server when Spotlight is monitoring both SQL Server and Windows metrics.

When you ask Spotlight to monitor a SQL server, it installs a work database with stored procedures to assist data collection on each monitored instance and doesn't install an agent service to run on the system. The Enterprise edition looks at lots of data on the server: Performance Monitor, system tables, Data Management Views in SQL Server 2005 using native SQL Server, and OS instrumentation.

The Diagnostic Server that is part of Spotlight on SQL Server Enterprise is responsible for data collection and for communication to the instances of the console program that might be installed around the enterprise. The Diagnostic Server keeps the data it retrieves for one week, allowing you that much time to review detailed metrics in support of root cause analysis. Another component, installed separately but included with the basic license, is the Spotlight Reporting Option. In addition to reporting, the Reporting Option also lets Spotlight on SQL Server Enterprise users archive data to a SQL Server database for long-term trend analysis. Using the Reporting Option Configuration tool, you can easily create the database and enable automatic data collection. As for producing reports, Quest provides three sample reports for use with SSRS to get you started.

Installation

How many ways can you say easy? Spotlight on SQL Server Enterprise requires Windows Server 2003, XP, or Win2K with MDAC 2.8 and SQL Server 2000 Client Tools. Storage space on an instance of SQL Server is needed to use the Reporting Option. The Spotlight on SQL Server Enterprise Getting Started Guide outlined everything I needed to know. Upon starting up the console after running the installation routine, I was prompted to either connect to an existing Diagnostic Server or install a new one. I selected Install and the resulting wizard completed quickly. Just as easily, I used Spotlight on SQL Server Enterprise's discovery process to find and configure SQL servers for monitoring. For kicks, I reran the Diagnostic Server installation wizard, directing it to install the service on a remote server, and the process was just as clean. The documentation warns that error log scanning can affect the performance of the monitored server, and Spotlight on SQL Server lets you configure what to scan for in the error log.

The Reporting Option installs on the Diagnostic Server from a separate downloadable file. Once the Reporting Option is installed, you use the Spotlight Reporting Option Configuration tool to specify a target SQL Server instance for storage and, if necessary, create the database. A Configuration tab lets you select which classes of metrics will be archived and how often. A Connection tab lets you override the global defaults of the Configuration tab for specific connections.

The Spotlight Console

The Spotlight console offers several views of the activity of your monitored servers. The Connection view, which Figure 2 shows, displays the status of a single SQL server. The first thing you notice is the bright, active presentation. Indicators spin and flow to represent rates of activity and flash to draw your attention to metrics passing an alert threshold. The Spotlight Enterprise Today perspective lists current alarm conditions across all monitored connections.

Shops with a larger number of SQL servers will appreciate the Enterprise Viewer, which is simply another selectable view within the console. Enterprise Viewer presents you with a configurable high-level view of the status of your systems. Monitored servers appear as simple labeled circles that are color coded for the highest severity alarm currently raised on the server. You can create groups and subgroups of servers that display as a single icon, allowing you to represent the status of the largest enterprise on one screen. The Enterprise Viewer also let me create and save multiple named views, each with a different subset and grouping of servers.

Overall, I felt that the multiple entry points for the various views that Spotlight on SQL Server Enterprise can present detracted from its overall usability. I can see a need to hide inactive connections in a situation where you have a large number of monitored servers, but why not provide a simple check box to highlight open connections when all are displayed? The Enterprise Viewer could also be integrated more tightly into the console. The Enterprise Today view, which is really a filtered display of alarms, is a useful display, yet it's hidden in the "Welcome" group with Web links that are essentially gratuitous marketing, and displaying it hides your list of views or connections, adding an extra step to switch back out of. Separating OS- and SQL-related displays into two connections also clutters the selection column, especially for those with lots of servers.

No matter which view you're looking at, Spotlight on SQL Server Enterprise lets you know when a high-severity alarm is raised by flashing an iridescent red icon that includes the SQL Server instance. When Spotlight on SQL Server Enterprise is minimized or not the active focus of the monitor, a balloon pops up from the Task Bar notification icon with the alarm summary. To drill down into the alarm, simply expand the group until you get to the server raising the alarm, then display the home page for the flashing connection. Positioning the cursor over the server's primary status icon displays the alarm summary. From there you can quickly drill down into the various detail status pages that the alarm indicates to explore the root cause.

I asked Quest Software what overhead Spotlight imposes on a monitored server, and the answer was, "It depends." I was told that when a Spotlight console is connected to and actively monitoring a server, the overhead is in the range of 4 to 4.5 percent. The console calls for more frequent sampling than does the Diagnostic Server alone, which causes less than 1 percent CPU overhead.

A key benefit to Spotlight on SQL Server Enterprise is the ability to install the console at additional locations, each connecting to the same Diagnostic Server, so I installed a second Spotlight on SQL Server Enterprise console on an XP system. The installation was easy, and I pointed it to the Diagnostic Server I had set up previously.

Spotlight has a lot of flexibility as you define alarms. It allows you to define as many as seven severity levels of alarms for monitored metrics. Alerting/alarm reporting options are relatively limited: For each metric, email is the only external reporting option, and you are allowed to enter a single email address for notification. I liked Spotlight's ability to send an email message when it detects a "server down" condition.

Summary

Both of these products are usable and effective. Many people will like SQLdm's price point and features, and I found it to be a bit easier to use than Spotlight on SQL Server Enterprise. SQLdm does have its limitations, notably in the area of remote monitoring and multiple-console support. Spotlight has the edge here with its Console/Diagnostic Server architecture. Combined with the ability to create very high-level summary views of the health of the largest database farm, the largest installations may well look to Quest. It was a difficult decision, but my Editor's Choice for this review goes to SQL diagnostic manager for its usability features and likely appeal to a very broad audience.