Product Review: FMS Total SQL Analyzer

Monday Jan 14th 2002 by Danny Lesandrini

Danny Lesandrini explores FMS Total SQL Analyzer in his latest product review, focusing primarily on installation issues, help system/documentation, general ease of use and his observations on the overall benefits of the TSA utility.

Over the past couple of years I've enjoyed using several FMS utilities with Microsoft Access and Visual Basic, and so I was very anxious to see their first product designed for SQL Server. Prior to receiving my reviewer's copy, I checked out the FMS Total SQL Analyzer Web page, which does a great job of describing its features. It currently sells for an introductory price of $399 for a single user license. I encourage you to go to their Web site for a complete price list, description and product tour. My review will focus mostly on installation issues, help system/documentation, general ease of use and my observations on the overall benefits of the Total SQL Analyzer (TSA) utility.

High-level Product Overview

Briefly, TSA provides comprehensive documentation of all objects, properties and settings available in Microsoft SQL Server, along with analysis of potential issues and performance bottlenecks. The idea is simple: Collect metadata for your SQL Server, capturing information about the server itself and/or individual databases and saving this "snapshot" into a results file for future reference and comparison. These configuration and object snapshots may then be used to answer the following questions:

  • How has my SQL Server configuration changed over time?
  • What does my object schema look like?
  • How have specific objects changed over time?
  • What hidden issues affect reliability, performance and scalability?
As you might imagine, there is a ton of metadata associated with even the simplest of servers. Fortunately, TSA has a great system for filtering metadata so you don't have to plod through information that's of no interest to you. The utility allows you to navigate the object hiearchy (see illustration below), checking properties of individual objects as you go, and TSA boasts a report system with more than 70 different reports.

Installation Issues

The Total SQL Analyzer application was a breeze to install, as all software installs should be. From the time I placed the CD in the drive until I was examining database objects less than 15 minutes had elapsed. Just for kicks, I ran the uninstall, restarted my system and reinstalled TSA. Everything ran without a hitch. The reinstalled application even located my previously saved results files, and I was able to resume navigating metadata without rerunning the utility.

Another nice feature, which I'll lump with installation, is the update wizard, which uses your Internet connection to check for product updates. Since the product is new, there were no updates to download, but I appreciate a program that includes a utility to take care of this necessary process for you.

Help System and Documentation

Like most of my friends, I hate to read instructions in general, and that goes double for software documentation. I love it when I can simply navigate the menu system and figure out how to use an application. That's how Total SQL Analyzer worked for me. The menus are simple and clear, and the wizard is easy to follow.

The online help system is not verbose, but is useful and to the point. It actually makes for easy reading, kind of like a user manual. (By the way, a 100-page user manual actually comes with the product. You don't see that every day!) On the other hand, the context sensitive help (activated by pressing F1) is not very granular. By granular, I have in mind the VB style context sensitive help. For example, in VB, when you place the cursor on a property in the property window and press F1, you are taken to a specific help article about that property. In TSA pressing F1 takes you to the appropriate article in the help system, but the information is specific to the screen and not the active control.

General Ease of Use

As mentioned above, I was quickly able to analyze my favorite database after a cursory inspection of the various menu options. The explorer style left-hand menu was very intuitive and made browsing database objects fast and easy. The right-hand screen refreshed quickly as I moved from object to object and corresponding metadata was logically partitioned in the tab control style interface into Summary, Properties, Issues and Performance Tips.

For example, while exploring the Summary tab for the Authors table in an analysis of the Pubs database, I noticed a property named "Script". Double-clicking this property brought up a dialog with the SQL DDL script necessary to create the Authors table. I copied the script to the SQL Server Query Analyzer, changed Authors to Authors2 and executed it. The script ran without error, creating a clone of the Authors table named Authors2. That held true for other scripts I tested for stored procedures, views and triggers. The same was true of script text copied from the report interface.

Speaking of reports, they really are quite comprehensive. The report filter interface is intuitive and powerful. You can collect metadata for the server and all its databases, and run reports for a single object type (say, tables) for a single database. The reports are clean and somewhat customizable. Like most documenters, they tend to be verbose, producing very large documents. As you might expect, the Report List contained numerous detail reports that, while nice for browsing, would probably not need to be printed. As a developer, there were a number of reports, such as the Database Summary, Table Dictionary and Table Indexes reports that would be handy to keep around in printed form ... and to store for future reference as a snapshot in time of these important properties.

My Observations

Overall this is a great tool. Some may object, suggesting that you get the same functionality in SQL 2000 Query Analyzer, but if that's how you view this utility, you're missing the point. Its greatest strength lies in preserving a set of document snapshots for database object comparison over time.

The report interface is excellent, though the magnitude of some of these reports (one in excess of 400 pages) causes browsing from front to back to be very slow, even on my PII, 500 Mhz machine. For shorter reports (less than 100 pages) the response for advancing pages was noticeable, but acceptable.

The most disappointing feature for me, as a developer, was the Issues and Performance Tips. Towards the bottom of the product fact sheet it lists the 14 issues and 11 performance tips that the utility checks for. Having run it against 3 different databases, I found only a couple of changes I would make to improve the performance of my database. Now, one could argue (and I think I will) that the lack of performance suggestions indicates that my databases are well designed. That may be the case, and it is rewarding to be told that you shouldn't change a thing, but I hope that future releases of this product enhance the Issues and Performance Tips feature.

What's the Verdict

So, what's the verdict? I like this utility and I'll make it a habit to run it against my production databases on a regular basis. There are some issues, as described above, that I hope get worked on for product updates and future releases, but I'd have to say that Total SQL Analyzer already is a "ready for prime time" product.

See All Articles by Columnist Danny Lesandrini