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.