Hey Guys,
Ran into another issue with SSRS this week and thought I'd share the solution. We had a report that had 2 rows and it was taking upwards of 2-10 minutes to render. It would run fast through Epicor and then it would sit there spinning...
After some digging we found out that the Data Retrieval time was taking an inordinate amount of time.
You can look at this by running the following query in yor ReportServer DB
Select * From Executionlog3
ORder by TimeStart DESC
Our Data Retrieval time for this report was > 90000, after doing some poking around it was suggested that it might be because of Parameter Sniffing (see this link if you are curious https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/)
It was suggested that adding a recompile hint at the end of the query would force MSSQL to generate a new execution plan every time the query is called and thus it would generate a more optimal planÂ
We added OPTION (RECOMPILE) at the end of our statement and our data retrieval time went down to 2... http://i.imgur.com/IuPI7GB.png
So if you are having issues with SSRS Performance, it may be worth taking a look at this in addition to the other issues
The report now runs in a few milliseconds.
Jose C Gomez
Software Engineer
T: 904.469.1524 mobile
Quis custodiet ipsos custodes?