This is the last in my series on monitoring performance on a server hosting Microsoft’s SQL Server Reporting Service. In Part 1 I show a way to monitor reports that are running. In Part 2 I shared a few queries to see historical report performance. In Part 3 I discuss building an SSAS cube that lets me analyze the performance in Excel or SSRS reports. Today I’m going to look on the reporting server itself.
A good place to start is with report server performance counters. Some of the ones I keep a close eye on are the Requests Queued, Requests Executing, Requests Rejected, and Tasks Queued. Overall these counters give me a good picture of how active the server is. Continuous high values in any of them may tell me that some reports may be running sluggish. Another similar counter is Active Connections. Ordinarily this number is pretty small, but I’ve seen issues when a high number of active connections caused CPU and memory issues. Though I haven’t been able to pinpoint yet why it’s happening at least I get a heads up that it’s about to. Memory Pressure State is also useful. and I’ll discuss that a little more later. You can view the full list of available counters on Books Online.
Secondly, look at the report server error logs. Anytime a report runs it’s logged in these files. Usually it’s just the fact that the report was processed. But if you see reports that show a status other than rsSuccess in the ExecutionLog2 view the reason is probably here as well. in this screenshot I can identify a report that probably didn’t render fully as there’s a data type mismatch, most likely in the data source of the report. Then I can check other times this report ran to see if there were similar errors. That could help in determining if the fault is with the report or with the data for the time this report ran. Remember that you can see the report parameters back in the ExecutionLog2 view.
Ted Krueger, a SQL MVP who writes for LessThanDot (Blog | Twitter) wrote an excellent post on the logs here. And Books Online also shows you how to control the logs in Server Trace Log; what info to store, how long to store them, and where they can be found.
Finally, check out the settings in the rsreportserver.config file. Again, Books Online has a breakdown of each option (RSReportServer Configuration File). Some of the settings here can really be helpful in getting the best performance out of your SSRS server. Remember the Memory Pressure State performance counter I mentioned earlier? Here is where you can define what memory pressure is. There are some great articles online about what each option is used for. One of the best (Exploring the RSReportServer.config File) was written by Jes Schultz Borland (Blog | Twitter) for SQL University.
I hope I was able to help out a bit. Definitely check out the links I’ve provided if you need more information. All those authors have written about SSRS much better than I can.
There’s another very useful post on Reporting Services Performance Optimizations on the SQLCAT site. Especially helpful is the section on Memory Configurations on both SQL 2008 and SQL 2005 instances.
Also, check out Configuring Available Memory for Report Server Applications in Books Online.