Reporting Services 2005 - Performance Counter Guidance

Traditionally we have shied away from noting specific values or thresholds that are indicative of good or bad performance.† One reason for this is because coming up with good values is quite hard to do, and people sometimes see that a particular value is outside of some threshold and become fixated on that being the issue when in reality it may not be.† For example, the Windows NT Resource Kit had a section that stated that a disk queue length greater than two to three times the number of disk spindles was indicative of a performance problem.† When working with SQL Server this is not always true, especially if read ahead activity is driving the disk queue length. Just because there is a queue waiting for IO does not necessarily mean that SQL Server is stalled waiting for the IO to complete.† We have seen disk queue lengths up in the 20-30 range (on much fewer than 10 disks) where SQL Server performance was just fine.

However, in the absence of specific values people sometimes look at Performance Monitor data and fail to spot interesting trends.† So despite the hesitation to provide any specific values, we will attempt to note some thresholds where a given counter starts to attract the interest of those who have been working some of these cases over the past several years.† Hopefully this will provide you some guidance in things that stand out.† But ultimately, you will need to look for other counters that also seem out of range so that you can start developing a pattern of indicators that point to a particular type of problem.

It should be fairly easy for you to visually identify a counter whose value changed substantially during a problematic time period.† Quite often you will find that there are many counters that changed significantly.† With a blocking problem, for example, you might see user connections, lock waits and lock wait time all increase while batch requests/sec decreases.† If you focused solely on a particular counter (or a few counters) you might come to some very different conclusions about what the problem is, and you could very likely be wrong.† Some of the changes in counter values are the cause of the original problem, whereas others are just side affects from that problem.†

In the ideal situation, the change in the counters that indicate the cause of the problem should lead the counters showing the affect, but due to the granularity used to capture Performance Monitor data some of these distinctions can be lost.† If you collect data once every 15 seconds and the problem was of quick onset, it can be hard to figure out if user connections went up first and then lock timeouts, or vice versa.† This is where you have to use other available information, such as other performance counters, the customerís description of the problem, etc, to form a theory as to what you think may be wrong and then look for other supporting data to prove or disprove your theory.

The most important counters are in blue.

It is recommended to save the counters to a CSV file or a SQL Server database.

The sample rate should be every 15 seconds.

 

This is the list of the SQL Server counters only. Please also use the Windows Server Performance Counters.

 

 

Counter

Preferred Value

Description

Active Sessions

 

Number of active sessions. This counter provides a count of all browser sessions.

Cache Hits/Sec

 

Number of requests per second for cached reports.

Cache Misses/Sec

 

Number of requests per second that failed to return a report from cache. Use this counter to find out whether the resources used for caching (disk or memory) are sufficient.

First Session Requests/Sec

 

Number of new user sessions that are started from the report server cache each second.

Memory Cache Hits/Sec

 

Number of times per second that reports were retrieved from the in-memory cache. In-memory cache is a part of the cache that stores reports in CPU memory. When in-memory cache is used, the report server does not query SQL Server for cached content.

Memory Cache Misses/Sec

 

Number of times per second that reports could not be retrieved from the in-memory cache.

Next Session Requests/Sec

 

Number of requests per second for reports that are open in an existing session.

Report Requests

 

Number of reports that are currently active and being handled by the report server.

Reports Executed/Sec

 

Number of successful report executions per second. This counter provides statistics on report volume. Use this counter with Request/Sec to compare execution to report requests that can be returned from cache.

Requests/Sec

 

Number of requests per second made to the report server. This counter tracks all types of requests that are handled by the report server.

Total Cache Hits

 

Total number of requests for reports from cache since the service started. This counter is reset whenever ASP.NET stops the Web service.

Total Memory Cache Hits

 

Total number of cached reports that were returned from the in-memory cache since the service started. This counter is reset whenever ASP.NET stops the Web service. In-memory cache is a part of the cache that stores reports in CPU memory. When in-memory cache is used, the report server does not query SQL Server for cached content.

Total Memory Cache Misses

 

Total number of cache misses against the in-memory cache since the service started. This counter is reset whenever ASP.NET stops the Web service.

Total Processing Failures

 

Total number of report processing failures that have occurred since the service started. This counter is reset whenever ASP.NET stops the Web service. Processing failures can originate from the report processor or any extension.

Performance Counters for SQL Server Reporting Services 2005†† ††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††

( SQL Server Premier Field Engineers in Microsoft UK )