August 12, 2013 at 6:47 am
Hi All,
When I am trying to open ssrs report, for the first time its taking more than a minute after that its opening very quickly 2 to 3 seconds. Can anyone please tell me the reason and the solution.
Thanks,
Suresh.
August 12, 2013 at 7:08 am
Prevent SQL Server Reporting Services Slow Startup[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 9:19 am
SSRS will go to sleep after about 20 minutes of no-one using it. The next person who uses it will wait for about a minute for SSRS to wak eup before their report starts to run.
Most of the information on the web about how to overcome this relates to SSRS 2005 and it not relevant to SSRS 2008 and above. I have tried the Recycle Time change suggested in the previous post, but it merely delays when SSRS goes to sleep.
Something I have done at one place I worked at that gets around the problem is to set up a SQL Agent job that runs every 10 minutes and opens a report.
I do not have the code available so cannot send it to you, but Google can find you details of how to open a report in a command line, and you can probaby work out how to put that command line into a SQL Agent job.
The end result of this is that SSRS is kept alive and ready to run reports all the time. SSRS will do a forced close about once every 24 hours to release any memory that has leaked, but the SQL Agent job will often get it back before anyone notices.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 13, 2013 at 3:31 pm
our company has a large number of SQL servers (over 850), each with thier own SSRS instance. The main issue we had was the SQL Server database instance "Maximum server memory" was unbounded, which allowed it to comsume almost all of the available server memory. This resulted in many of our SSRS services being starved of memory. Set a reasonable maximum memory limit for the SQL Server database instance.
August 14, 2013 at 10:27 am
This could also be something as simple as a cold cache vs. warm cache in the database the report queries. The first time the user runs a report and SSRS sends the query to the DB, the data pages the DB needs to execute the query are not in the buffer pool, so it reads them from disk (a comparatively slow process). Since pages aren't flushed from the buffer pool until SQL Server needs the space, the next time the user runs the report, at least some of the data pages may still be in the buffer pool. If the DB server is well-provisioned with RAM and/or there's not much activity in the SQL Server instance, all of the data may still be in the buffer pool the next time the report runs. Data retrieval from memory is much faster than data retrieval from disk.
Jason Wolfkill
August 14, 2013 at 10:51 am
wolfkillj (8/14/2013)
This could also be something as simple as a cold cache vs. warm cache in the database the report queries. The first time the user runs a report and SSRS sends the query to the DB, the data pages the DB needs to execute the query are not in the buffer pool, so it reads them from disk (a comparatively slow process). Since pages aren't flushed from the buffer pool until SQL Server needs the space, the next time the user runs the report, at least some of the data pages may still be in the buffer pool. If the DB server is well-provisioned with RAM and/or there's not much activity in the SQL Server instance, all of the data may still be in the buffer pool the next time the report runs. Data retrieval from memory is much faster than data retrieval from disk.
Possibly, but SSRS (Report Manager) is known for it's slow startup after a time of inactivity. Has something to do with libraries loading or something like that.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 15, 2013 at 1:33 pm
I'm not a .NET programmer but my understanding is that .NET applications (which SSRS would be) compile at run time. That may figure in here somewhere.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply