Report generation is very slow the first time the report is run

Question

Hi All, I am having an issue rendering reports. I am on SSRS 2019 latest patch 15.0.8760.20928 When I run a report from the web interface SSRS a report can take 2 minutes to render. I then re-run the same report and it renders in 10 seconds. This happens on most reports.
I saw a post about keeping the website alive by running a simple subscription every so often. I figured I would test this simple theory, but it does not seem to be doing anything.
I saw another post about running the below PowerShell script. I am not sure if it is a memory hog or not.
I have checked the indexes on all tables being hit and all look good. the actual Stored Proc that runs the report run in a second in SSMS. I don't see any latency between the VM in AZURE and my managed instance. Any help would be greatly appreciated! TIA, Joe

# Root url of SSRS Report Manager
# Example:
# http://localhost/Reports for a local default SSRS instance
# http://servername/Reports_SQLEXPRESS for a remote named SSRS instance "SQLEXPRESS"
[string] $url = "https://<yourserver>/Reports"; #Change to the URL of your SSRS Server

# Create new WebClient
$wc = New-Object System.Net.WebClient

# Set the user agent to a known browser
$wc.Headers.Add("user-agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36")

# Loop forever
while ($true) {
    # Download the page
    $wc.DownloadString($url)

    # Wait for 5 minutes
    Start-Sleep -Seconds 300
}

Answer 1

Hi @Joe Fischer,

To find out how long it takes to process a report and other usage metrics, review the report server execution log. For more information, see Report Server ExecutionLog and the ExecutionLog3 View.

Also, you may use SSRS snapshot. A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server.

Add a Snapshot to Report History

You may also refer to this blog to see if it is helpful.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.