Return to ProgrammingMSAccess.com home page

A Review of Total SQL Analyzer PRO

Like the ubiquitous Swiss Army Knife, FMS’ Total SQL Analyzer PRO contains a multiplicity of tools for the comprehensive analysis and documentation of SQL Server 7.0 and SQL Server 2000 databases. The product is organized around the concept of a “job”, which defines the analyses to be performed. When a job is run the output produced is termed “job results”. The entire user interface is skillfully designed around these fundamental constructs of job and job results. Users manipulate these constructs to readily define jobs as well as view and print job results. Once defined, jobs may be revised, and/or run repeatedly, to track the evolution and development of changes in a database. A job definition is limited to a single server.

The product’s analyses include more than 120 performance optimizations, configuration and design issues. Objects to be included in the analyses for a particular job are selected by the user from a menu tree in defining a job. This menu tree very thoughtfully follows the same basic structure that is used in SQL Server, so anyone who already knows SQL Server will be familiar with its contents.

There is an excellent Create New Job Wizard, with very thorough documentation, which I found effective in guiding me through the process of defining a job. The following screen-shot, illustrating the object selection process for a new job, gives some indication of the various objects available for analysis.

There are a number of variations on the above selection screen that allow the user to designate filters to provide for the selection of specific subsets of these objects.

The results of a job run are documented in more than 80 reports available through the user interface. The user has the option to view reports onscreen or print them. With such voluminous output available in job results it is necessary to provide some disciplined mechanism to access this information. To accomplish this, reports are organized by categories, which may also be selected from a menu tree similar to that shown above. Some of the available report categories, along with a brief description of each, are:

Databases	Information about scripts, object inventory, database lists and properties and settings.
Dependencies	Object dependency or cross-reference information, including where objects are used and objects used by a given object.
Issues	Issues detected by Total SQL Analyzer PRO in the documented objects
Performance	Performance suggestions detected by Total SQL Analyzer PRO in the objects selected in the job.
Servers	Information about the Microsoft SQL Server object itself, including server properties and object inventories.
Stored Procedures	Information about stored procedures including properties and TSQL
Tables	Information about tables including properties, columns, triggers, keys and indexes

I found the detailed listing of scripts for all database objects, along with issues for each, to be an invaluable documentation guide to the database design features. Another favorite is the dependencies report, which illustrates all the relationships among the objects in the database.

In summary, a very unique and comprehensive, although somewhat pricey, documentation and analysis tool for SQL Server 7.0 and SQL Server 2000.

Reviewer biography:

Sid Singer contributed this review as a member of the Database Developers Group. He started Singer & Associates in 1983 to assist clients in using modern information technology as a strategic business tool. He is an independent consultant, ICCA member, who focuses on applications development with VB/VBA, Access and SQL Server. You can contact Sid Singer for consulting support at singsid@compuserve.com.

In-depth vendor-supplied whitepaper on this product.

Return to ProgrammingMSAccess.com home page