At month-end when multiple users are running the same report, sporatically the stored procedure will fail to return data for the report. The stored procedure returns data in the query window, and the problem is fixed by re-generating the stored proc (script it as alter into a query wendow and execute it). Has anyone else had this happen? If so is there a solution?
asked Nov 03, 2011 at 06:20 AM in Default
I believe after sometime you may be getting timeouts. This is where no data may be coming.
Now as far as the procedure is concerned, there I think may be issue of parameter sniffing. Obviously, at month-ends the parameters supplied could be totally different from daily procedure call. I believe this is where a new execution plan produces the output. In our environment, we have maintenance window in which some procedures are recompiled, statistics are updated, indexes re-organized/re-build are done before moth-end routines. So my suggestion would be to re-compile this procedure before month-end routines.
If you can provide us the procedure/tables DDL and parameters (for routine and month-end), we will be able to help you better.
answered Nov 03, 2011 at 06:45 AM