SQL Reporting Service Slow on first report

Posted on 2012-09-19
Last Modified: 2012-10-16

I have SQL Server 2008 R2 installed on Server 2008 R2 and I’m finding that the reporting service when it loads up the first report of the day is rather slow. After the first report has been generated if it is in constant use it will remain fast. Is there any way to speed up the process when it has been sad idle for a while or to stop it from going idle?
Question by:Everymancomputers
    LVL 35

    Expert Comment

    The first initial access is slower anyway. After the first access, the RS loads the results into the cache and it looks faster after that.

    Especially if you run complex reports, which need processiong time.

    For complex reports, you may enable the preprocessing of the resports. (Properties of the Report in RS Web) Means the reports are not generated on the fly but in the background. This way the reports are in the cache before the user accesses them. The lack of this is, that the reports are not exactly on the fly anymore, but you can set a time scope, when they are refreshed...
    LVL 21

    Expert Comment

    by:Alpesh Patel
    You can cache the report or create snap shot for that to speed up report from Report Manager.
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    This might help:

    If autoclose was off already you might be able to schedule a report every hour to keep things awake. You can use a report schedule if you have SQL Standard or higher, with Express you can use a vb script and a scheduled task.
    LVL 25

    Expert Comment

    If you profiler trace the queries of your first run then you can see in which queries the time goes.  Compare it to a second run.

    If time difference is in reading the data from your reporting database (report definition) or from your database which contains the data you know where to look for improvement.

    If your statistics are out of date, you have missing or fragmented indexes  then you read to much into your cache and the frist time that takes time ... afterwards while data is in cache it goes quicker as long as that data stays in cache (so regularly used).
    LVL 37

    Expert Comment

    To avoid the slow "Woohoo, I'm the first report of the day!" load, you need to "warm up" your report server.

    Have a look at the following article where the issue is described:

    It also contains a vbs script that can be used to time the initial warm-up.  The bottom part of that script (starting at "Getting ready to instantiate the RS web service") can be used to warm up your server. Schedule it through the SQL Agent at some time in the morning, about 5 minutes before the first user will request a report.

    I'll copy the crucial part here as reference:

    'Getting ready to instantiate the RS web service
    ts = Now()
    Dim rs As New RS2005.ReportingService2005
    Dim creds As New Net.NetworkCredential
    creds = Net.CredentialCache.DefaultCredentials
    rs.Credentials = creds
    rs.Url = "http://localhost/reportserver_r2/reportservice2005.asmx"
    rs.ListChildren("/", False)
    'Instantiated the RS web service

    Open in new window

    Pay attention to the URL, you'll probably need to customize that to your situation.
    LVL 37

    Accepted Solution

    Another, probably more elegant, option is to increase the RecycleTime in the rsreportserver.config.  By default this is set to 720 minutes (12 hours).


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
    You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
    This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
    This tutorial will walk an individual through the process of upgrading their existing Backup Exec 2012 to 2014. Either install the CD\DVD into the drive and let it auto-start, or browse to the drive and double-click the Browser file: Select the ap…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now