question

TedSchnieders-4065 avatar image
0 Votes"
TedSchnieders-4065 asked DelahuntyJames-1788 commented

SQL Server Reporting services 2019 Slow on new server

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
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

   <ConnectionOpenTime>22365</ConnectionOpenTime>  makes up a majority of the time of executing each report.  THis is seen even which I go in and hit test connection on the data source.  It spends for 22 seconds before it comes back and indicates success.  If I hit it again another 22 seconds.  All reports that use a stored credentials leveraging a data source that uses domain authentication has the additional 22 seconds before it starts to run the report.

<AdditionalInfo>
<ProcessingEngine>2</ProcessingEngine>
<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
<EstimatedMemoryUsageKB>
<Pagination>3</Pagination>
<Processing>1</Processing>
</EstimatedMemoryUsageKB>
<DataExtension>
<SQL>2</SQL>
</DataExtension>
<Connections>
<Connection>
<ConnectionOpenTime>22365</ConnectionOpenTime>
<DataSets>
<DataSet>
<Name>DataSet1</Name>
<RowsRead>0</RowsRead>
<TotalTimeDataRetrieval>657</TotalTimeDataRetrieval>
<ExecuteReaderTime>657</ExecuteReaderTime>
</DataSet>
</DataSets>
</Connection>
</Connections>
</AdditionalInfo>

0 Votes 0 ·

Also, connecting to these instances via SSMS is immediate.

0 Votes 0 ·
Show more comments
Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered

Hi @TedSchnieders-4065 ,
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichelKampOpslogix avatar image
0 Votes"
MichelKampOpslogix answered DelahuntyJames-1788 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Michel,

Did you ever fix your issue? As I too have the same problem and can not find a solution.

thanks JamesD

0 Votes 0 ·