SSRS 2019 reports are painfully slow
-
Hi.
I'm migrating reports from an SSRS implementation on SQL Server 2012 to SQL Server 2019, and the reports render painfully slow on 2019. We're talking minutes. I've scoured the internet and it seems like this is a common question, but the answers point toward making the DB queries more efficient, using Stored Procedures, etc - but I have this problem even when the report doesn't pull any data from SQL Server.
I created a Test Report that has a single text field parameter, and the dataset is literally just:
SELECT
*
FROM
(VALUES ('test 1', 'test 2', 'test 3')) E(test,test2,test3)When I click on the icon to load the report, it takes 25 seconds just to load the parameter screen. If I type something in and click "View Report" the report can take up to a minute to return the "report" with those 3 static values.
This occurs for all users, and any browser.
Any ideas or suggestions as to why SSRS is slow on my new server? While the report is rendering, it's not stressing the cpu or memory on that server in the least... 4% cpu, no change in memory... it doesn't even seem like the browser is trying to pull data.
Thanks for your help!
-
Just a little more info. Attached are the Logs from ReportServer DB for this test report.
You can see the time for data retrieval, processing, and rendering are nil, but the total duration is consistently around 45 seconds total. What the heck is taking up the rest of that time?
-
Network? Busy-ness of the machine it's on?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The machine is more-or-less pre-production... so very little is happening on it. Resource Monitor shows 0% Network Utilization the whole time the report loads. Disk is 1-2% Highest Active Time. CPU usage is 2-4%
-
Have you verified the anti-virus software has been setup to exclude SQL data files, log files, the SSRS folders and web services?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
There's actually no anti-virus setup yet. It's still pre-production and is not internet- accessible.
-
Is this a stand-alone system where you have both the database engine and reporting services installed? Is this server hosted in Azure by chance?
Since this is pre-production is it in a different subnet or domain? Anything about the setup and configuration where it would have issues with name resolution - or network routing?
Are you running the reports on the server directly - or through the network from your workstation? If from your workstation - do you have the same experience when running the reports directly on the server?
Just throwing out different ideas...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hey Jeffrey,
Sorry for the delay in getting back. It was a crazy busy week and this fell down a few priority notches.
I ended up solving it over the weekend. I fixed it by simply uninstalling SSRS 2019 and reinstalling it and setting it up again. :-/
I went that route because I had tried everything else. This was a stand-alone server... the databases and interface were on the same machine. I had tried it both on prem before moving the vm to Azure... it had been both on the same subnet and in Azure's... and it was slow both from a workstation and on the server.
But after the install it's running like a champ. Thanks for your help.
-
Thanks for the feedback
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply