Having a finely-tuned engine is the key to achieving the best gas mileage. Better gas mileage means fewer trips for fill-ups. Having a finely-tuned reporting solution is the key to great performance in terms of the speed of information retrieval. This means avoiding embarrassing conversations with your end users about how slow reports are running. Many aspects of a reporting solution can influence the performance of the entire solution. I cover some of these areas in the following sections.
Benchmarking system performance
Before system performance can be monitored, you must know what level of performance is reasonable given normal working conditions. To do this, establish a server performance baseline by monitoring the report server performance at regular intervals, even when no problems occur.
Searching for performance bottlenecks
Demand on system resources may become extreme enough to cause bottlenecks for the following reasons:
Resources are insufficient and additional or upgraded components are required.
Resources are not sharing workloads evenly and need to be balanced.
A resource is malfunctioning and needs to be replaced.
A program is monopolizing a particular resource; this might require substituting another program, having a developer rewrite the program, adding or upgrading resources, or running the program during periods of low demand.
A resource is incorrectly configured and configuration settings need to be changed.
Tuning system performance
Insufficient memory is the most common cause of serious performance problems in computer systems. If you suspect other problems, check memory counters to rule out a memory shortage. Poor response time on a workstation is most likely the result of memory and processor problems; servers are more susceptible to disk and network problems. Before you start tuning, consider the following recommendations:
Make one change at a time. In some cases, a problem that appears to relate to a single component might be the result of bottlenecks involving multiple components. For this reason, it is important to address problems individually. Making multiple changes simultaneously may make it impossible to assess the impact of each individual change.
Repeat monitoring after every change. This is important for understanding the effect of the change and to determine whether additional changes are required. Proceed methodically, making only one change at a time to the identified resource and then testing the effects of the changes on performance. Because tuning changes can affect other resources, it is important to keep a record of the changes you make and review the record after you make each change.
Review event logs. In addition to monitoring, review these logs because some performance problems generate output you can display in Event Viewer. For more information about using Event Viewer, see the earlier section, "Using Event Viewer."
Compare the performance of network programs to locally run programs. This can help you determine whether network components are playing a part in performance problems.
The previous tip was excerpted from Chapter 14, 'Optimizing Report Performance,' from the book "Microsoft SQL Server 2005 Reporting Services for Dummies" by Mark Robinson, courtesy of Wiley Publishing. Click here for the complete collection of book excerpts.
Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning