Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Reporting services first call is very slow Expand / Collapse
Author
Message
Posted Monday, August 12, 2013 6:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 1:34 AM
Points: 27, 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.
Post #1483272
Posted Monday, August 12, 2013 7:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 13,270, Visits: 10,148
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1483284
Posted Tuesday, August 13, 2013 9:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 2,858, Visits: 3,180
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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1483805
Posted Tuesday, August 13, 2013 3:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:48 AM
Points: 37, Visits: 119
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.
Post #1484008
Posted Wednesday, August 14, 2013 10:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
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
Post #1484396
Posted Wednesday, August 14, 2013 10:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 13,270, Visits: 10,148
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1484401
Posted Thursday, August 15, 2013 1:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:52 PM
Points: 184, Visits: 1,072
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.
Post #1484900
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse