Sneak Preview: Quest's Central 5.5

A database administrator has enough to handle in supporting a single database platform. Supporting heterogeneous platforms means more challenges and requires a knowledge of multiple toolsets and languages. Quest Software's Quest Central 5.5 carries on the product's heritage of helping database admins manage multiple tasks with a common workflow across various platforms, taking some of those added challenges off the administrator's plate.

Tool Suite

Quest Central is available for IBM DB2, Microsoft SQL Server, Oracle and Sybase database management systems. Most of Quest Central's tools support Microsoft SQL Server and Oracle, but IBM's DB2 is well-represented, too. Sybase is the least supported of the four.

I put the tools through their paces using our Green Bay, Wis., NWC Inc. business applications lab, taking advantage of the lab's Oracle9i corporate database. Click here for more on our live 24/7 production environment and lab.I installed the client components for SQL Server on a Windows XP SP2 system and the Oracle components on a Windows 2000 machine.

The main tools in Quest Central are Spotlight, a performance diagnostics tool; SQL Tuning, which helps adjust SQL statements; Performance Analysis, which lets you monitor current and historical performance; Database Analysis, which determines best practices and efficiencies that can be gained by following them; and Space Management, which lets you optimize storage capacity.

Installation went smoothly, but I had to reinstall Performance Analysis to get it to show up in Quest Central's menu. Existing Quest Central users must purchase Quest's Toad 8.6 for some Oracle database administration functionality; a trial version comes with Quest Central.

Fast FindDatabase admins will like Spotlight's ability to quickly and easily discover performance problems in real time. It continually monitors different activities--such as active sessions, CPU usage, memory usage, server processes and disk storage--for the database platform and the operating system, then displays its findings graphically. The speed and color of the animations change to reflect database activities and alarms that occur when an activity has surpassed a configured threshold.

I ran Spotlight against SQL Server and Oracle and watched the counters and alarms react when I created activity on the server. I set the CPU utilization threshold to a 15 percent average over a 30-second period and when that line was crossed, the alarm was raised. A click on the alarm directed me to drill down into other areas of Spotlight, such as the processes and session data. In addition, Spotlight keeps a history of past system performance that record can be played back to help diagnose performance problems.

Fine Tuning

SQL statement optimization has been automated in the SQL Tuning tool. I created several individual queries for optimization, one of which had several joins. When run through SQL Tuning, the tool found 24 different ways to rewrite that query. I sorted the new queries by elapsed execution time and found the best-performing alternative to my original by a few milliseconds. However, sometimes the optimized SQL statement isn't as intuitive to the average user as the original code and may not be as easily maintained.

The SQL Scanner tool, which can be used to identify SQL Tuning candidates, lets you search for unoptimized SQL statements through database objects that contain SQL statements, such as triggers and stored procedures, trace files and several other sources. I ran a scan on several of our database objects and found many the scanner indicated were problematic, which I could import into the SQL Tuning tool.But SQL Scanner, which the company gained as part of a recent acquisition, is not integrated into Quest Central, and its user interface seemed less intuitive than other parts of the product. After you select the objects to search, for example, they move into a Job Manager. From there, you must again select which of these statements should be scanned. Quest says it plans to integrate this piece more fully in future versions.

The Snapshots feature in the Database Analysis tool is more robust in this version, according to Quest. You can take snapshots of your database server and then run an analysis on those snapshots, and Quest says snapshots continue even if its underlying collection fails.

I ran a snapshot on SQL Server and Oracle servers that contained several databases and then performed the analysis, which runs predefined checks in areas such as performance and best practices.

The action plan items returned included information such as where wide-clustered indexes existed, a notice that there was no password for the system administrator account, and reports on several other standard database best practices that were not being followed. For each check there is a recommended action along with background information. It ranks each item as a low-, medium- or high-priority effort, as determined by the software.

Performance ChecksThe powerful Performance Analysis tool lets you monitor current and historical activity, telling you what the CPU, I/O, and lock and log wait times were during database slowdowns. Historical diagnostic reports can be e-mailed automatically. The tool requires a server-side component called StealthCollect, which takes a small but adjustable toll on your server.

With StealthCollect running on the server, I started up Performance Analysis and ran through several database actions to see which elements were causing the biggest wait times for the statements being run. I then drilled into the session with the most activity and tracked which queries were contributing to the long wait times. I could send these queries to the SQL Tuning tool to see if they could be optimized.

Another feature I like is Space Manager, which let me graphically view fragmentation of tables and indexes and reorganize any database on the server. The LiveReorg feature let me reorganize the database objects. There are also capacity reports you can run to determine how much space is being used and when that space will be exhausted.

Another handy feature in the tool set is Knowledge Xpert. It offers information such as the syntax of SQL commands, or how to perform various administrative tasks. I found that it covered most areas, though only some in depth. It's good as a quick reference but won't replace your database platform documentation.

One of the big advantages of this software is that you can use it to administer and troubleshoot problems without being an expert at a particular database platform. There are a few shortcomings, including unhelpful error messages when I came face to face with some unexpected errors. But there is a significant amount of value in the latest version of Quest's software, giving a database administrator much more power, along with quick access to key information to help keep databases running smoothly.Cory Cundy is an applications developer for a software company. Write to him at [email protected].