Speed Coefficient provides deep, meaningful analysis of SQL Server

Stellar reporting shines in Imceda's graphical database trace tool

Troubleshooting your database system when it's having problems is no longer enough. With the recent onslaught of audit requirements such as Sarbanes-Oxley, DBAs must stay on top of system and query performance on a regular basis in order to keep up with their environment as changes occur.

With the 2.1 release of Speed Coefficient, Imceda has greatly increased its graphical SQL Server trace tool's performance, adding features that extend Coefficient's baselining and troubleshooting capabilities well beyond those of Microsoft's SQL Profiler. Without a doubt, this is the most useful trace tool I have ever seen.

Coefficient uses the same trace mechanisms that SQL Profiler does. In fact, a trace performed in Coefficient will produce the same trace files that one in SQL Profiler would. But unlike SQL Profiler, Coefficient loads the trace files into a database and aggregates the queries and their execution stats to give you an accurate, detailed picture of what's going on within your database. It even picks through the miscellaneous SQL calls you would ordinarily have to pick through by hand. (These miscellaneous calls are all the unnecessary system chatter, such as "set ANSI Nulls on" and "select @@version," that have nothing to do with your applications.)

Coefficient installs very easily; a few clicks and you're done. Furthermore, it requires only a client-side install; nothing is needed on the server side.

After I installed Coefficient, I traced a server under very high user load. The process proved simple. You can configure Coefficient to trace in either Express or Advanced mode. Express mode asks for minimal input, (such as duration, and whether to run a lightweight or a full trace), then it gets you on your way quickly. Advanced mode allows you to fine-tune every aspect of your trace; I was able to include and exclude users, programs, and different trace elements, as well as set an amount of time for the trace to run. Coefficient also comes with a plethora of default trace templates, and it allows you to define your own.

You may also choose to have Coefficient automatically analyze the data after a trace is complete. You can customize Coefficient's predefined analysis templates to provide the level of detail you require and to filter out all but the specific problems you're looking for. You may, for example, choose to see only those queries that use more than a certain amount of the CPU or that run over a certain time, or you may choose to see only security information or deadlocks.

As the analysis engine runs, it shows you what it's doing every step of the way, which is especially helpful if a trace takes longer than expected. This feature is a significant improvement on the last version, in which the analysis wizard simply told you that it was "analyzing data." Now you easily can be certain that your trace is making progress.

Coefficient's tracing and analysis capabilities are standard; its true power resides in its reporting. Coefficient generates easy-to-read charts and tables, depicting some of the most intimate statistics of your database. In a simple frame view, you can select subject areas and drill down deeply into details.

Coefficient shows you details about stored procedure calls, such as call frequency, duration, CPU utilization, reads, writes, and more. Each of these detailed sections is headed by a pie chart that shows the summary values of the counter being measured, followed by a detailed table of values that you can sort according to heading.

There is also a very useful section that details both direct and indirect stored procedure calls. Coefficient will tell you whether the procedure was called directly by an app or by another procedure.

Coefficient also provides intelligent analysis. Its SQL Health section analyzes server workload and tells you how your stored procedures should be performing given the workload involved. The procedures that need attention are marked in red.

Two of the more useful sections that go hand in hand in determining system performance are Top Worst Calls and Stored Procedure Call Frequency. The Top Worst Calls section tells you which stored procedures have the poorest performance, separating results by several metrics, including CPU, duration, reads, and writes. Coefficient then tells you how important the Top Worst Calls are by showing you how many times each one was called in the Stored Procedure Call Frequency section.

For example, a stored procedure that takes three minutes and is called four times a day isn't as important as a stored procedure that takes 35 seconds to run and is called an average of 20 times a minute.

The tool also displays detailed information about queries inside stored procedures. Coefficient shows not only the performance of each procedure but also the individual queries inside — as well as the execution plan and outdated column statistics of each query. Details about stored procedures are also provided for noncompiled SQL queries (which the tool refers to as dynamic SQL queries).

Moreover, Coefficient shows you many of the same details for user connections. You can see which users comprise the largest percentage of database connections, use the most CPU, or perform the most reads or writes. Coefficient also gives detailed information about caching, time-outs, errors, exceptions, and warnings.

Coefficient does have its limitations. It doesn't compare two separate traces, nor does it have a central repository. Coefficient also doesn't allow for easy customization of the front end, nor does it allow you to schedule recurring traces. But Imceda says it will add these features and more later this year.

Overall, Coefficient does a much better job than the competition, and it's definitely worth investing in while it's still young. For analyzing the database activity on your server, there is no easier or more complete tool than Speed Coefficient.

InfoWorld Scorecard
Performance (20.0%)
Value (10.0%)
Setup (10.0%)
Manageability (25.0%)
Reliability (15.0%)
Ease of use (20.0%)
Overall Score (100%)
Imceda Speed Coefficient 2.1 8.0 8.0 9.0 9.0 9.0 9.0 8.7