Product Review: FMS Total SQL Analyzer



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

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles