SQL 2012, the reports are displayed on a WEB page or rendered to PDF
I have an ongoing problem with reports suddenly slowing down. The only way to "fix" this problem is to restart the service. This occurs after an unknown number of days passing, seemingly at random one of my reports will start timing out in the application (must be under 5 minutes). The report that starts failing may normally run in two to three seconds or one to two minutes but now Reporting Service does not reply with in the available window. When profiling the procedure call being executed by the report is returning the data in a timely fashion but then nothing is returned to the application.
The last time this happened I started a profile and found this, I see the procedure call for the report return in 400 milliseconds. The report is then rendered about five minutes later. In this interval Report server queries for the parameters of the requested report 4 to 6 times, I see sessions for the report created and deleted, checks for snapshots (snapshots turned off for this report).
As soon as the Reporting Services service is restarted the report returns to its normal behavior Request to Render 3 seconds. I looked at the execution logs and found that from the service side reports it spent all of its time getting the data from the database but the profiler is telling me a different story. If I run the same report from Reporting Server Management I get the same slow response so I do not think it has anything to do with the app that is requesting the report.
Things I have tried; uploading the report again, rewrite the procedure to improve performance, for the currently miss performing report. Nothing helps but restarting the service.
Has anyone seen this, if so how did you address this issue? This issue happened in SQL 2008R2 as well but not as often about ever 6 months and to make matters worst it is happening more frequently. Over the last month it occurred 3 times.
The stats are up to date. The procedure ran at normal speed in SSMS, about one second for the procedure for the report. I did not look at waits. It may be that the reports database was experiencing waits but I am not sure why restarting the service would resolve the problem. Other reports were not experiencing any slow down so it seems unlikely there were issues with waits.
The report server does not slow down just one report does. Other report requests are completed normally. Performance monitor shows no increase in CPU usage, no uptick in IO, network activity stays normal.
Best Answer
I believe problem was that the report definitions no longer matched the stored procedures that were called. After I refreshed all of my reports the problem seems to be resolved.
Unfortunately I can not say for sure that is the final answer as it could be up to 90 days between the last restart and as little as 4 days, so I am still waiting to remove this from my task list.
These are old reports that were originally in version 2005.