SSRS Report Performance issue after Upgrade

Question

SSRS Report Performance issue after Upgrade

ralahari

Old Hand

Points: 301
More actions
October 11, 2023 at 6:47 pm

#4306081

We Upgraded db from MSSQL 2008 to Microsoft SQL Server 2019 & SSRS Server 2008 to 2014
While running reports , we are facing performance issues report not loading after 30 min also , We did Query optimization it is taking 1 - 5 min in SSMS Based on Parameters.
In the report server , we are getting rsHttpRuntimeClientDisconnectionError in below table
SELECT * FROM dbo.ExecutionLog3 where
Status not in ('rsSuccess')
ORDER BY TimeStart DESC
Could you Please guide us how to resolve this issue.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16133 More actions · Answer 1

had lots of those as well - even after increasing the timeout significantly they were getting similar errors after 90 min - as we were only using SSRS to generate some new reports (not my choice) we ended up using c# (closedxml) to generate those.

interested in knowing the solution to this.

Jo Pattyn SSC-Dedicated Points: 32269 More actions · Answer 2

That's a big jump, somewhere around sql 2014 the optimizer plan generation changed.

Have you tried different compatiblity modes?

Have you checked https://www.sommarskog.se/query-plan-mysteries.html

*edit*

as you are on SQL 2019, have you enabled querystore to view / monitor the problematic queries

frederico_fonseca SSCoach Points: 16133 More actions · Answer 3

on my case the issues we had was not on the data retrieval but on rendering and processing - which is done after all data has been loaded.

the OP didn't post the output of the command but that view has a few columns that do help identify issues.

TimeDataRetrieval - Number of milliseconds spent retrieving the data.

TimeProcessing - Number of milliseconds spent processing the report.

TimeRendering - Number of milliseconds spent rendering the report.

where the first option is very high that could indicate a issue with QE optimizer.

ralahari Old Hand Points: 301 More actions · Answer 4

Hi In My Case as well Query is running in 1 Min, But TimeProcessing to taking 48 Min,

Could you Please let us know how to Reduce TimeProcessing,

In My Report I have One Row Grouping and 5 Column level grouping we getting total of 40 Columns as well 2 vbscript functions running on to of 4 columns.