SQL diagnostic manager 5.0

Idera’s SQL diagnostic manager (SQLdm) 4.6.8 was one of two products I reviewed in the article "SQL Performance Monitoring and Management Tools," April 2007, InstantDoc ID 95281. I was impressed with SQLdm 4.6.8’s monitoring and drill-down capabilities. SQLdm 5.0, which I reviewed here, retains the best of the earlier version, while major architecture and UI changes now enable you to use SQLdm more effectively. You can use SQLdm 5.0 to monitor and manage your SQL Server instances so that you can quickly diagnose and resolve database performance problems.

As with the earlier version, SQLdm 5.0 installs nothing on the monitored server—no agents, services, databases, or stored procedures. It uses SQL Server’s native monitoring interfaces—Distributed Management Objects (DMO), Server Management Objects (SMO), remote queries, and (when turned on) a remote lightweight trace—to obtain information from monitored servers. Note that SQLdm 5.0 lets you install its three key components (the SQLdm 5.0 console, the Services server, and the data repository) on separate systems, adding support for remote consoles and new data collection options. The product installs the management and monitoring services on the Services server. SQLdm 5.0’s monitoring service retrieves data from monitored servers and stores all the collected data in a SQL Server instance (not included with SQLdm 5.0), eliminating the flat files SQLdm previously used to store the most recent week’s worth of monitored data. Also, the GUI management interface now supports remote access by multiple users. SQLdm 5.0’s components can be installed on Windows 2000 and later, including the x64 versions of Windows and the IA64 platform. SQLdm 5.0 monitors SQL Server 2005/2000 instances.

The default installation creates the repository database on the SQL Server instance you’ve designated, and installs SQLdm 5.0's console and management and collection services on the local machine. The product is easy to install and includes the option to install the prerequisite Microsoft .NET Framework 2.0. Note that you’ll typically only use SQLdm 5.0's custom installation option to install the console on other workstations.

SQLdm 5.0’s console has a new look and feel, making it easy to work with. You can use the product's console to not only explore the status of monitored SQL Server instances, but also to configure all aspects of SQLdm 5.0’s operation, such as specifying the SQL Server instances you want to monitor, the frequency of data collection and data retention periods, and conditions that generate alerts and associated notifications.

Web Figure 1 shows SQLdm 5.0’s console, which has an organization similar to Microsoft Office Outlook 2007 and includes a tabbed information display section on the right when working in the Servers view. The My Views section lets you quickly limit the servers displayed to a subset of the monitored servers. SQLdm 5.0’s default views include the All Servers view and the views that display servers in the state corresponding to their name: Critical, Warning, and OK. Servers take on the state of the most severe pending alert. Note that you can create a named view to include a specific subset of the monitored servers. The default display for each view provides an overview of the status of each server, as shown in Web Figure 1. You can view a brief summary of active alerts by hovering your mouse over the server’s status icon. The icons on the left of each server’s display take you to corresponding detail tabs for that server.

Upon installation, SQLdm 5.0 is preconfigured to monitor and publish alerts for key SQL Server and OS metrics. Customizing the metric threshold values that generate alerts will often be one of the first configuration tasks you’ll want to undertake, and SQLdm makes this easy to do. The value of each metric can fall into one of three states: OK, Warning, or Critical. A right-click menu from any alert display takes you to the alert’s configuration screen, where you can drag a slider to alter the Warning and Critical thresholds for numeric metrics or select the Default Alert Configuration check box to enable state-based alerts. You can view the full list of available metrics and associated alerts from the Tools menu.

For each alert, you can configure SQLdm 5.0 to generate three types of notifications: via email, by creating an event in the application event log of the Services server, or by adding an entry in SQLdm 5.0’s To Do list. SQLdm 5.0's To Do list reminds you to take action, and can encourage SQLdm console operators to take a closer look at specific alert conditions. When you create a To Do notification for an alert, you assign it to an Owner. The Owner name is really a free-form text field that you can use to filter a To Do list display. To Do list entries are created in a Not Started status, which SQLdm 5.0 console operators can change to In Progress, On Hold, or Completed.

You can explore the status of a server while investigating the circumstances surrounding an alert in the Servers view. You can view an active display of key metrics by selecting the Overview tab, and then clicking the Summary button, as shown in Web Figure 2. The display is color-coded by the alert status of related metrics—for example, in Web Figure 2, red indicates a critical processor bottleneck because the processor queue length metric of 20 is in the alert’s Critical range. By expanding the All Servers tree view or selecting another tabbed view at the top of the server’s display pane, you can access more detailed server information. For example, you can click Details on the Overview tab to view the current values and configured thresholds for all the monitored metrics above a running graph of the metrics that you can add to the bottom of the Details view by clicking a check box. You can click the Sessions tab to view session details, filter the display to show only locking or blocking sessions, and kill or initiate a trace for a selected session. The Database tab provides rapid access to database, table, and file configuration and status information; lists of known SQL Server database backup and restore operations; and the ability to update statistics or rebuild indexes. If you enable the product’s trace facility, SQLdm 5.0 will provide query statistics, including the most frequently used queries and worst-performing queries.

The product’s built-in reporting options let you graph key sets of metrics and export the graphs in Microsoft Excel. SQLdm 5.0 includes 10 sets of predefined graphs. Just select the server(s), database(s), time period, and sample size, and SQLdm 5.0 will generate the graphs for you.

Throughout SQLdm 5.0, product documentation and Help information (provided in PDF and compiled Help file—CHM—formats) is readily available, useful, and complete. You can also add tips and reminders to the alert definition using the Comments field, which could be a boon to other members of your team when dealing with a problem you’ve already worked your way through.

I was impressed by SQLdm 5.0’s completeness and responsiveness. Although you can't add additional metrics to the set that SQLdm 5.0 monitors, SQLdm 5.0 includes everything you need to detect and diagnose most performance problems affecting your SQL Server instances. SQLdm 5.0's console is well organized, and many displays include shortcuts to related displays, making it easy to move from one display to another. I was also impressed by the console’s responsiveness—SQLdm 5.0 displays new screens much more quickly, at least in my opinion, than moving between screens within SQL Server Enterprise Manager.

SQL diagnostic manager 5.0
PROS: SQLdm’s well-designed console is responsive and easy to navigate; provides key metrics and full configuration information for servers, databases, files, and tables; there’s nothing to install on monitored SQL server instances; includes rich documentation and Help information; all metrics are stored on a SQL Server you provide; automatically purges old data
CONS: Limited predefined reporting
RATING: 5 stars
PRICE: $1595 per SQL Server instance
RECOMMENDATION: SQLdm 5.0’s completeness and ease of use makes it easy to recommend—try it out, you won’t be disappointed.
CONTACT: Idera ? www.idera.com ? 877-464-3372