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

Joe Fischer 0 Reputation points
2024-01-25T15:40:14.6566667+00:00

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
}
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,735 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 30,881 Reputation points
    2024-01-26T02:44:08.1433333+00:00

    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.

    0 comments No comments