Improving the performance of your database can be a huge and costly task. To achieve this, there are various tools available to accomplish this, including those included with SQL Server, and third-party tools. So, why you want to use a third-party tool instead of the free tools included with SQL Server? There are many reasons, which you will discuss in this article.
Quest Software, (who recently purchased Imceda Software), a developer of a wide range of SQL Server tools, has released a new version of Coefficient, version 2.2.0. Coefficient was awarded Best Database Performance Tool by www.sqlservercentral.com Readers’ Choice 2004, and SQL Server Magazine Readers’ Choice. So does this application live up to all of its hype? Let’s find out.
Version and Configuration
This review was done using the following software and hardware configuration:
- Operating System: Windows 2000 Advanced Server, Service Pack 4
- SQL Server: 2000, Service Pack 4
- Coefficient 2.2.0
- Processor: Intel Pentium CPU 2.80 GHz
- Memory: 512 MB
Coefficient System Requirements
- Internet Explorer version 5.5 SP1 or higher
- Microsoft Data Access Components (MDAC) version 2.7 SP10
- SQL Server 2000 databases running in “6.5”, “7.0”, or “8.0” compatibility modes. SQL Server 7.0 databases must be run in native “7.0” compatibility mode.
New Features and Fixes
There were major enhancements to Coefficient 2.2.0 from 2.0. Following are a few of these enhancements for those who may be familiar with previous versions.
- Coefficient 2.2.0 includes a comprehensive Table-Index Scan Analysis. This scan analysis performs a deep examination of all table scans, clustered index scans, and index scan operations and displays the summary results by table. Users can also generate a detailed scan analysis pages which display all execution plans and SQL statements that caused scan operations on each table.
- SQL statements that trigger scan operations are now included in the Min/Max Execution pages for the detail scan analysis option.
- Users can define the threshold for the number of rows a table must have in order to be included in the table-index scan analysis.
- Users can now define the number of queries to include in the Top Worst Calls analysis section.
- The Coefficient query processor has improved to handle function calls in SQL statements. Users will now see all the function calls for a SQL statement in the analysis.
- Import and Export functionality was added for trace tables from which users can export a trace table with optional compression and encryption.
- Users can import a trace file which has been created from tools like SQL Profiler into the Coefficient database.
- Coefficient now caches trace table information in the user-interface. Moving from database to database, or from server to server, no longer requires Coefficient re-fetch trace table information from the work database, which has improved the flexibility of users to work with Coefficient.
- Numeric sorting capabilities have been added to the user-interface for the Trace Size and Trace Duration columns, which has improved usability.
Other than the above new additions, there are additional fixes to improve the stability and usability of Coefficient.
Product Overview
Coefficient has the ability to monitor the performance of a remote database server. To do this, it creates a database on the selected server to store the data is collects and analyzes.
Coefficient 2.2.0 can be divided into main three categories with respect to its functionalities, which include Trace, Analysis, and Templates. All the functionalities are driven by simple wizards which reduces the complexity of the work. The Trace option is used to create traces for your databases. The Analysis option is used to analyze the created traces. Templates are pre-saved option so that users can use them without having to create their own. This will be discussed in more detail in this review.
Before Coefficient can analyze database performance, it must first collect a trace of database activity. There are two modes of creating traces: Express Mode and Advanced Mode.
The Express Mode runs with default values. This is the easiest mode available to start a trace. With this mode, a trace will start just after the configuring the trace. The Express Mode does not give user the flexibility of changing the trace applications or users being traced.
The Advanced Mode allows you to customize the trace, if want to specify trace criteria or schedule the trace. Even though in this mode users have more flexibility than the Express Mode, configuring it is bit more difficult than the Express Mode.
I have used Advanced Mode to demonstrate the capabilities of Coefficient for this article.