Monitoring and Performance Tuning

Because Reporting Services uses everything from IIS to e-mail and the files system, there a lot of places to monitor and tune for performance.

Just like performance tuning in any major application, it is a matter of divide and conquer. The process I have explained in other chapters still holds: Detail the parts of the application, monitor for bottlenecks, determine the components within that part of the system, and tune for performance.

You can find many resources for monitoring from the client through the IIS layer, so I do not repeat those here. The important elements for performance within Reporting Services have more to do with the databases that it uses as well as the source databases and the Reporting Services connection to them.

For the source databases, you should follow two basic rules: Always use a separate database for reporting source data than the OLTP system, and optimize those databases for reads. That means a high fillfactor and a lot of indexes. The idea behind these concepts is to reduce contention and to allow the fastest access possible to the source data.

Make sure you have maintenance plans set up on both Reporting Services databases as well as the separate source database. Other than standard database tuning, you want to optimize the path between the Reporting Services servers and the source database. If you have split several Reporting Services servers to talk to a central catalog server, ensure the path is optimized between those systems, too.

You have several additional objects and counters available in Windows System Monitor (PerfMon) to watch the Reporting Services server. They are divided into the two mechanisms I mentioned at the beginning of the chapter: the Windows service and the Web service.

For the Web service, you can watch the following counters in the MSRS 2005 Web Service object. Cache objects show memory pressure, actions per second show the load on the server. The more times things are received directly from cache, the better.

Counter	Used to Show
Active Sessions	Active sessions; includes all browser sessions generated from report subscriptions, whether they are currently active or not.
Cache Hits/Sec	The number of requests for all cached reports.
Cache Misses/Sec	The number times that a report was not in cache.
First Session Requests/Sec	The number of new user sessions started from cache.
Memory Cache Hits/Sec	The number of times that reports are retrieved directly from memory cache.
Memory Cache Misses/Sec	Reports that could not be retrieved from the in-memory cache.
Next Session Requests/Sec	Reports that are opened in an existing session.
Report Requests	Reports that are active and being processed by the report server.
Reports Executed/Sec	Successful report executions.
Requests/Sec	All types of requests sent to the report server.
Total Cache Hits	Requests for reports from the cache since the service started.
Total Cache Misses	Report requests that were not in the cache since the service started.
Total Memory Cache Hits	Reports returned directly from memory cache since the service started.
Total Memory Cache Misses	Cache misses since the service started.
Total Processing Failures	Report processing failures since the service started.
Total Rejected Threads	Processing threads switched from asynchronous processing to synchronous, serial processes.
Total Reports Executed	Total number of reports that ran successfully since the service started.
Total Requests	Total number of all requests made to the report server since the service started.

This table shows the counters for the Windows service in the MSRS 2005 Windows Service object. The same logic holds true, activities per second show load and the cache objects show memory use. More items found in cache show proper memory sizing.

Counter	Used to Show
Active Sessions	Active sessions in the report server database.
Cache Flushes/Sec	Number of times the cache flushes.
Cache Hits/Sec	Requests for cached, re-rendered reports.
Cache Misses/Sec	Requests that fail to return the report from cache.
Delivers/Sec	Report deliveries from any extension.
Events/Sec	Events such as SnapshotUpdated and TimedSubscription that are processed.
Memory Cache Hits/Sec	Number of times reports are retrieved from in-memory cache.
Memory Cache Misses/Sec	Number of times that reports cannot be retrieved from in-memory cache.
Next Session Requests/Sec	Requests for reports that are open in a session.
Report Requests	Reports that are active and handled by the report server.
Reports Executed/Sec	Reports successfully generated.
Total App Domain Recycles	Application domain cycles since the service started.
Total Cache Flushes	Report server cache updates since the service started.
Total Cache Hits	Reports processed directly from memory cache since the service started.
Total Cache Misses	Reports not processed directly from memory cache since the service started.
Total Deliveries	Reports delivered for all extensions by the Scheduling and Delivery Processor.
Total Events	Events processed since the service started.
Total Memory Cache Hits	Cached reports returned directly from memory cache since the service started.
Total Memory Misses	Cache Cached reports returned directly from memory cache since the service started.
Total Processing Failures	Processing failures that have occurred since the service started.
Total Rejected Threads	Processing threads switched from asynchronous processing to synchronous, serial processes.
Total Reports Executed	Total number of reports that ran successfully since the service started.
Total Requests	Total number of all requests made to the report server since the service started.