How to diagnose issues when running reports in the report server?
A customer came to the SQL Server Customer lab to investigate a problem with one of the feature I’m responsible for – data-driven subscriptions. In looking at their solution, we discussed any number of problems they had encountered. I realized that it can be difficult to find out where to start looking when a problem occurs. This post will hopefully provide you a starting point as you endeavor to fix the issues you run into.
Reports can take up
- a lot of memory,
- a lot of time to execute,
- a lot of CPU
Generally speaking, it is possible for errors to occur as a result:
- Out of Memory
- Internal Errors
- Rendering errors
This begs the question, “How to diagnose issues when running reports in the report server?”
General process to follow:
For Report Execution problems, start with the report server execution log
This log will tell you which reports are failing, who ran them, what parameters they used
It will also provide the time at which it failed and the server in your deployment on which the report failed
You can use the time and server to find the actual stack trace in the trace log files
Reference the trace logs based on timestamps ranges you find in the event log
The trace logs provide detailed stack trace information.
You can sometimes understand what the error is based on this info
Search the Reporting Services MSDN forum using the stack trace information for solutions to the problem you encountered
You can find information on some of the error codes here:
Logging
- Report Server exposes a number of log files, these include:
o Information on report executions and whether they were successful and additional data related to the execution
o Detailed error stacks that show what the problems were
o Major events that occurred on your report server that you should be aware of are in the application event log in windows
- You can read up on all of this here:
o https://msdn2.microsoft.com/en-us/library/ms157403.aspx
Diagnosing processing and rendering problems – topic name “Processing Large Reports”
- https://msdn2.microsoft.com/en-US/library/ms159638.aspx
Monitoring Performance
- You will want to look at things like memory consumptions, application domain recycles, cpu usage, etc. To isolate problems you may adjust concurrency for the scheduling service so you know exactly which report is currently running (instructions are below).
- Application Domain recycles indicate the report server is under memory pressure. We use them to clean out memory. If your interactive report execution failed suddenly, and you're monitoring the performance counters for application domain recycles, you may see a correlation. You should also see information in the trace log related to this.
- This topic describes the performance counters
o https://msdn2.microsoft.com/en-us/library/aa972240(SQL.80).aspx
- Monitoring Report Execution Performance with Execution Logs
o https://msdn2.microsoft.com/en-us/library/aa964131.aspx
Specific actions to help diagnose problems:
- Adjusting Memory limits – see section “Report Size in Memory”
o If you’re seeing out of memory exceptions you can try increasing the memory used by report server
o https://msdn2.microsoft.com/en-US/library/ms156002.aspx
- Adjusting concurrency for Scheduling:
o While trying to determine what is happening, you might reduce the number of simultaneous report executions
§ If you’re always running extremely large reports, setting this to 1 will allow you to use all of the memory for the report
o In the file rsreportserver.config:
§ <MaxQueueThreads>0</MaxQueueThreads> determines concurrency for scheduling and delivery
§ “0” means the report server will determine the right number
Performance Whitepaper:
- It is recommended to hosting report server and the report server database and data sources from which you get report data on different computers
- https://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx
How to configure a scale-out deployment:
- Scale outs can increase throughput, reliability, and concurrency
- https://msdn2.microsoft.com/en-us/library/ms159114.aspx
Monitoring and triggering subscriptions:
- Sometimes making the report run on a schedule can help you isolate the performance issues (interactive report execution can lead to excessive load on your server)
· See ‘how to trigger a subscription’; this works on SQL 2000 RS, and SQL 2005 RS
· See ‘how to monitor a subscription’
Monitoring interactive report executions:
- The ListJobs SOAP API allows you to see which long running reports are currently executing
- https://msdn2.microsoft.com/en-gb/library/aa225969(SQL.80).aspX
If all else fails:
Use SQL Profiler to monitor the actions the report server is taking in the report server database
this is useful if you think the report server is not doing anything - you can watch the actual queries run through and watch report server respond to your actions
generally speaking, it is possible to see everything in our various log files, so there should be no need to go to this level
If you have additional questions, feel free to leave me a comment or post a question on my blog:
- Lukasz’s Blog: https://blogs.msdn.com/lukaszp
Take care and good luck,
-Lukasz