Why does a SSRS report time out when the Stored Procedure it is based on returns results within a few seconds?

Question

I have a report that renders data returned from a stored procedure. Using profiler I can catch the call to the stored procedure from the reporting services.

The report fails stating the report timed out yet I can execute the stored procedure from SSMS and it returns the data back in five to six seconds.

Note, in the example test run only two rows are returned to the report for rendering though within the stored procedure it may have been working over thousands or even millions of records in order to collate the result passed back to reporting services.

I know the stored procedure could be optimised more but I do not understand why SSRS would be timing out when the execution only seems to take a few seconds to execute from SSMS.

Also another issue has surfaced. If I recreate the stored procedure, the report starts to render perfectly fine again. That is fine except after a short period of time, the report starts timing out again.

The return of the time out seems to be related to new data being added into the main table the report is running against. In the example I was testing, just one hundred new records being inserted was enough to screw up the report.

I imagine more correctly its not the report that is the root cause. It is the stored procedure that is causing the time out when executed from SSRS.

Once it is timeing out again, I best fix I have so far is to recreate the stored procedure. This doesn't seem to be an ideal solution.

The problem also only seems to be occuring on our production environment. Our test and development platforms do not seem to be exhibiting the same problem. Though dev and test do not have the same volume of records as production.

Answer 1

The problem, as you described it, seems to come from variations on the execution plan of some parts in your stored procedure. Look at what statistics are kept on the tables used and how adding new rows affect them.

If you're adding a lot of rows at the end of the range of a column (think about adding autonumbers, or timestamps), the histogram for that column will become outdated rapidly. You can force an immediate update from T-SQL by executing the UPDATE STATISTICS statement.

Answer 2

sometime adding WITH RECOMPILE option to the CREATE statement of stored procedure helps. This is effective in situations when the number of records explored by the procedure changes in the way that the original execution plan is not optimal.

Answer 3

Basically all I've done so far was to optimise the sproc a bit more and it seems to at least temporarily solve the problem.

I would still like to know what the difference is between calling the sproc from SSMS and SSRS.