Increase SQL Server's Performance with Speed Coefficient 2.2

After running the analysis, an HTML report of the analysis is created. Because the report is in a HTML file, it can be easily distributed to other concerned parties. For example, if you want to display this report on an intranet, it is a simple task.

In the following screen shot, you see the results of a Coefficient analysis done on a trace using the Full Detail template, one of the many templates available.

The following report shows the percentage of reads done by the SPs captured in the trace.

If you feel a stored procedure is designed as efficiently as possible, then examine the Missing Column Statistics section of the analysis. Missing Column Statistics will help you to identify if there are any missing statistics that could be used by the Query Optimizer to speed the execution of the code. Evaluate that section, and if changes are made to the database, run another trace and see if the stored procedure has reduced its number of reads.

The following image shows the SQL Health of your database. SQL Health is a recommended set of queries to tune based on the CPU utilization found during the trace. Using statistics, such as overall SQL Server CPU utilization, a recommended list of stored procedures and SQL statements is displayed for review. Using this information, users can quickly identify those stored procedures and SQL statements which might benefit the most from performance tuning.

The following section shows connection information organized by SQL user name. Users can use this information to determine which SQL users were connected to the database and their activity totals.

The screen below shows how often the various stored procedures were executed, as well as general performance statistics about each stored procedure. Users can use this information to determine which stored procedures are executed most often.

Procedures that are executed frequently make good candidates for performance enhancements. Reducing any statistic (duration, reads, writes, or CPU) on a frequently executed stored procedure can sometimes have a dramatic effect on overall performance. Doing so, you can reduce the load on the server, thereby reducing transaction response time.

All stored procedures that have a high call frequency (see below) can be examined for possible performance enhancements.

Apart from reports described above, there are many more reports available, like a listing of Top Worst Calls, SQL Call Frequency, and SQL CPU Usage, among others. Top Worst calls give users indications of the heavy calls to the database. SQL call frequency gives the graphical output of how frequently SPs are used.

Coefficient can not only analyze traces that it collects directly, you also have the option of importing and exporting traces files in the CSV format. Exporting to the CSV format is valuable when users need to send the traces file across locations.


Leave a comment

Your email address will not be published.