SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Reporting services first call is very slow


SQL Reporting services first call is very slow

Author
Message
suresh.muppidi
suresh.muppidi
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 157
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.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62029 Visits: 13297
Prevent SQL Server Reporting Services Slow Startup


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
EdVassie
EdVassie
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13913 Visits: 3894
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
twells43
twells43
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 159
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.
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2678 Visits: 2582
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
Blog: SQLSouth
Twitter: @SQLSouth
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62029 Visits: 13297
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
jshahan
jshahan
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1046 Visits: 2159
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search