1

I have a stored procedure retrieving a large amount of data (two hundred thousand records). The SP works fine in SSMS with a run time at around 12 seconds. I am running an SSRS report with the SP, which works fine in SSRS preview, it takes around 30 seconds. When I run the SSRS Report via the SSRS URL access it hangs for about half an hour before crashing with a

Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500

Although the SP crunches a lot of data it only returns a small amount to the report (circa 200 rows max).

I have ensured that my SP has local parameters and all input parameters are passed through these for use (which I believe helps with parameter sniffing?).

I have checked that the SSRS preview is not retrieving cached data by clearing down the cache.

I have tried DBCC DROPCLEANBUFFERS; and DBCC FREEPROCCACHE to see if that makes a difference.

I checked log using Select * From Executionlog2 and found that the TimeDataRetrival = 32550, TimeProcessing = 29 and TimeRendering = 1798778.

It looks to me like the report is taking ages to render rather than retrieving data, which given the low number of rows passed to the renderer is strange.

Has anyone got any ideas what my next course of action/investigation should be?

sqlnovice
  • 11
  • 3
  • It's probably blowing up inside the browser, 200k rows + html rendering + dom etc is almost more than the average Facebook payload, so it's gonna suck. – Stu Feb 03 '22 at 20:54
  • The report is not rendering 200,000 rows; that is the the number of rows (approx.) that the SP processes to create the output. The report outputs calculated data and runs to about 200 rows max. It's a tiny report. – sqlnovice Feb 03 '22 at 23:36
  • I can run the same report with the same parameter inside the 'preview' of the VS development environment and it runs and renders just fine. It's only having trouble when run from a URL. – sqlnovice Feb 03 '22 at 23:39

2 Answers2

0

After a long and procrastinated investigation I could find no reason for the report rendering taking ages. In the end I created a new report and added items incrementally, continually testing to narrow down what was causing the render issues. In the end I had recreated the report in its entirety and the 'new' report runs fine. The only thing I can take from this is that there is something slightly 'off' on the original that is tripping the renderer and causing some sort of delay.

sqlnovice
  • 11
  • 3
0

The answer to this question may give you diagnostic ideas to resolve this SSRS web UI issued that generated the 500 HTTP status code.

If the report query results were reduced to a smaller subset, the web server failure (500) might be avoided.

JohnH
  • 1,920
  • 4
  • 25
  • 32