June 2, 2016 at 6:18 am
Our SSRS server normally runs our reports very quickly. Sometimes those reports will suddenly take a minute to load (vs 10 seconds) and only a restart of the SSRS service will fix it. After that reports load at their normal speed.
Any ideas on what's causing this?
June 2, 2016 at 7:36 am
I would hazard a guess that it's hardware limitations. Are you running sufficient RAM on your server? Are you running any other services apart from SQL on the server?
SQL Server has a habit of "nomming" on as much RAM as it can, and if you don't have a lot, it'll leave little else for other processes, such as SSRS. It could be that SSRS has been left with little physical RAM, and is using page files, which are A LOT slower.
What version of SQL Server are you running (for example, 2008 Standard, 2012 Business Intelligence, 2014 Enterprise), and how much RAM does your server have available to it?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 2, 2016 at 7:46 am
Look in the ExecutionLog table in the ReportServer database, and compare the time to run the query with the time to render the report. This will give you an idea of where the bottleneck is. Are the databases you're reporting against on the same server as SSRS? If they are, your job is a little harder, since the two are competing for the same resources. But at least you should get some idea about whether to check for blocking and tune queries, or cut back on some of the report functionality.
As Thom suggested, your problem could be with memory. If SQL Server and SSRS are sharing, make sure you set a reasonable max server memory for SQL Server.
John
June 20, 2016 at 4:13 am
Sorry for hacking an old thread.
I had this issue last week. The ExecutionLog was showing 8+ seconds for "data" retreival, but running the TSQL was instant. This was same for 30+ reports that normally processed the data in sub 1 second.
Logs showed no errors, VM was performing within normal thresholds. We tried to vMotion the guest to another machine to rule our host based issues. This did not resolve the issues.
As in the initial post, rebooting resolved the issues. Now, dont get me worng I'm "old skool" and would always advise a re-boot event if it was a locked user account. However, this has never happed to me before in the few years of using SSRS 🙂
June 20, 2016 at 5:04 am
I have seen issues when the reporting services service spikes and remains @100 Percentage for no logical reason as nothing is running . Did you check how much CPU the services was using.
June 20, 2016 at 6:19 am
HI,
They CPU was at 6% throughout the day and RAM untilisation was in normal parameters.
Thanks
January 10, 2023 at 1:58 pm
Hello,
Apologies to necro this old thread but I have recently encountered this problem on SSRS2017.
SQL Server is on a separate machine.
The same behavior where running a set of reports progressively become slower until it hits what seems like a upper limit.
For example reports that take 10s data retrieval, 20 second rendering eventually reach ~ 2 min total.
Did anyone ever find a resolution outside of restarting the service?
Cheers,
G
January 10, 2023 at 5:06 pm
Hello,
Apologies to necro this old thread but I have recently encountered this problem on SSRS2017.
SQL Server is on a separate machine.
The same behavior where running a set of reports progressively become slower until it hits what seems like a upper limit.
For example reports that take 10s data retrieval, 20 second rendering eventually reach ~ 2 min total.
Did anyone ever find a resolution outside of restarting the service?
Cheers,
G
Suggest you start a new thread on this question 😉
😎
A +6 year old question will hardly have too many in the audience!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply