SQL Server Reporting services 2019 Slow on new server

Ted Schnieders 6 Reputation points
2021-07-10T03:33:51.787+00:00

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>

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,773 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Joyzhao-MSFT 15,561 Reputation points
    2021-07-12T03:34:13.997+00:00

    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.

    0 comments No comments

  2. Michel Kamp [Opslogix] 1 Reputation point
    2021-10-15T13:51:22.647+00:00

    @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.