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?
CodePudding user response：
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.
CodePudding user response：
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.