SQL Server Reporting services 2019 Slow on new server

Question

Moving SSRS from Windows Server 2016 to new Windows Server 2019. Looking at the XML from ExecutionLog3 (Below). Connections take around 20 seconds before it moves on to actually running the report, instant on 2016 server. Testing data sources takes around 20 seconds using domain authentication, using sql authentication, oracle authentication, db2, all instant on both servers. SPNs have been moved to new server and service accounts, when I connect the report server database it does use Kerberos.

I was upgrading the report server to the latest 2019 version, which was just dot versions higher then the 2016 install. Uninstalled and installed the same version of SSRS as on the 2016 server, same experience as kind of expected.

I have turned on verbose logging on the report server, I can not see the issue.

HTTP Log

Server Name:

Software: Report Server Version 2018.0150.1102.675 ((BI_Server_Main).191031-2025)

Version: 1.0

Date: 07/10/2021-01:52:33

Fields: date time c-ip cs-username s-ip s-port s-host cs-method cs-uri-stem cs-uri-query sc-status cs-bytes time-taken cs-version cs(User-Agent) cs(Cookie) sc(Cookie) cs(Referrer)

07/10/2021 01:52:42 #.#.#.193 - #.#.#.99 20480 #.#.#.99 POST /ReportServer/ReportService2010.asmx 401 878 0 1.1 Mozilla/4.0 (compatible; MSIE 6.0; MS Web Services Client Protocol 4.0.30319.42000) - - -
07/10/2021 01:52:42 #.#.#.193 - #.#.#.99 20480 #.#.#.99 POST /ReportServer/ReportService2010.asmx 401 623 16 1.1 Mozilla/4.0 (compatible; MSIE 6.0; MS Web Services Client Protocol 4.0.30319.42000) - - -
07/10/2021 01:53:05 #.#.#.193 domain\user #.#.#.99 20480 #.#.#.99 POST /ReportServer/ReportService2010.asmx 200 1069 22453 1.1 Mozilla/4.0 (compatible; MSIE 6.0; MS Web Services Client Protocol 4.0.30319.42000) - - -

The last line shows my 22 second delay again. Not knowledgeable enough to know what it is trying to do, that is so slow.

Execution Log 3
<Connections>
<Connection>
<ConnectionOpenTime>21854</ConnectionOpenTime>
<DataSets>
<DataSet>
<Name>DataSet1</Name>
<RowsRead>176</RowsRead>
<TotalTimeDataRetrieval>3668</TotalTimeDataRetrieval>
<ExecuteReaderTime>3660</ExecuteReaderTime>
</DataSet>
</DataSets>
</Connection>

Answer 1

Hi @Ted Schnieders ,
Does it only happen the first time or everytime?
Report server performance is affected by a combination of factors that include hardware, number of concurrent users accessing reports, the amount of data in a report, and output format.
General principles to consider include the following:
Report processing and rendering are memory intensive operations. When possible, choose a computer that has a lot of memory.

• Hosting the report server and the report server database on separate computers tends to provide better performance than hosting both on a single high-end computer.
• If all reports are processing slowly, consider a scale-out deployment where multiple report server instances support a single report server database. For best results, use load balancing software to distribute requests evenly across the deployment.
• If a single report is processing slowly, tune report dataset queries if the report must run on demand. You might also consider using shared datasets that you can cache, caching the report, or running the report as a snapshot.
• If all reports process slowly in a specific format (for example, while rendering to PDF), consider file share delivery, adding more memory, or choosing a different format.
• 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.
• For more information about how to mitigate performance issues by tuning memory management configuration settings, see Configure Available Memory for Report Server Applications.
• Check DNS resolution, make sure to flush the cache first
• What is the configuration information of the report server?

Once you have successfully migrated your report server to a new instance, you might want to perform the following steps to remove programs and files that are no longer necessary.
Uninstall the previous version of Reporting Services if you no longer need it. This step does not delete the following items, but you can manually remove them if you no longer need them:

• The old Report Server database
• RsExec role
• Report Server service accounts
• Application pool for the Report Server Web service
• Virtual directories for Report Manager and the report server
• Report server log files

If none of these improve your performance issue, please provide more information about execution log 3 to diagnose report server performance.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@Ted Schnieders or @Joyzhao-MSFT did you ever solved this issue ?

We are having now the same issue. Brand new SRS 2019 installation. Datasource DB behind is fast, tested it by doing the SQL query used in the report manually. But the rendering and even opening the report before rendering is very slow.

I enabled all logging by flowing : report-server-executionlog-and-the-executionlog3-view

The strange part is that i don't have any ConnectionOpenTime or other connection info in the AdditionalInfo element ....
Does anybody has a idea how to get this info added ?

Thanks michel.

Answer 3

Hello from the future. I am posting this information in the event anyone has an issue similar to this/ours as it. I hope this helps someone out there. I suspect that the issue this was originally posted for is not the same since the root cause was related to Entra and other newer technologies Microsoft has developed since the original post, but the symptoms match, so I am posting here.

I came to this post when triaging an issue where our ConnectionOpenTime was very consistently between 21000 and 22000, with some (10%) of requests between 300 and 21000.

This occurred suddenly and impacted all (hundreds) of our SSRS reports at once. Our SSRS reports used a Windows AD Service account to authenticate to both the ReportServer database as well as each datasource database.

The root cause was that our admins unjoined our server from Entra for maintenance purposes (as of the writing of this, Intune cannot manage servers).

Because the server was previously joined to Entra, the AzureAdJoined flag was set to YES, and it was trying to authenticate to Entra first. The authentication to Entra was failing, and then falling back to on-prem authentication.

There were two fixes we could have chosen from:

1. Use SQL Server authentication
2. Follow the steps here to unjoin the server: https://learn.microsoft.com/en-us/entra/identity/devices/faq

The flag not being set when unjoining is on purpose, which I can understand if the device is offline at the time of unjoining, but I must not be understanding why the flag would not be set if the device is online at the time of unjoining. It seems like a bug to me, but I assume I am misunderstanding Microsoft's explanation in the above article (https://learn.microsoft.com/en-us/entra/identity/devices/faq#i-disabled-or-deleted-my-device--but-the-local-state-on-the-device-says-it-s-still-registered--what-should-i-do).

Above, Michel Kamp asks about why ConnectionOpenTime was not updated/provided in the AdditionalInfo element. During our debugging, a report was created that didn't query any datasource, it simply had a label/textbox on the report. This report still had the issue, but the ConnectionOpenTime was missing. For us, this was fixed when the Execution Account was using the Windows AD based service account (in the Report Server Configuration Manager).