SSRS2008R2 report times out, but the underlying query executes in the Management Studio
- by Matthew Belk
A customer of mine recently moved servers and the new server has SQL2008R2. His old server was SQL2005. The new server has substantially better CPU, RAM, and disk performance than the old, but several reports time out while executing.
When I run the underlying query in the SQL Management Studio, the query executes in sub-second time.
The exact error message returned via the Report Manager UI is:
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError)
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
It must be noted that this database is not just analytical; it's also fairly transactional, although the transaction volume is not exceptionally high.
What can I do to improve the performance of the SSRS query engine? Are there settings in the data source I can adjust, or in the SSRS config files?