SSRS2005 timeout error

I've been running around circles the last 2 days, trying to figure a problem in our customers live environment. I figured I might as well post it here, since google gave me very limited information on the error开发者_如何学编程 message (5 results to be exact).

The error boils down to a timeout when requesting a certain report in SSRS2005, when a certain parameter is used.

The deployment scenario is: Machine #1 Running reporting services (SQL2005, W2K3, IIS6) Machine #2 Running datawarehouse database (SQL2005, W2K3) which is the data source for #1 Both machines are running on the same vm cluster and LAN.

The report requests a fairly simple SP - lets called it sp(param $a, param $b). When requested with param $a filled, it executes correctly. When using param $b, it times out after the global timeout periode has passed.

If I run the stored procedure with param $b directly from sql management studio on #2, it returns the results perfectly fine (within 3-4s).

I've profiled the datawarehouse database on #2 and when param $b is used, the query from the reporting service to the database, never reaches #2.

The error message that I get upon timeout, when using param $b, when invoking the report directly from SSRS web interface is:

"An error has occurred during report processing. Cannot read the next data row for the data set DataSet. A severe error occurred on the current command. The results, if any, should be discarded. Operation cancelled by user."

The ExecutionLog for the SSRS does give me much information besides the error message rsProcessingAborted

I'm running out of ideas of how to nail this problem. So I would greatly appreciate any comments, suggestions or ideas.

Thanks in advance!

The first thing you need to do is to ensure your statistics are up to date.

(It sounds like a case of an incorrect query plan being used due to parameter sniffing, as described in this SO answer: Parameter Sniffing (or Spoofing) in SQL Server).

One way to fix this in SQL Server 2005, is using the OPTIMIZE FOR query hint. See also OPTIMIZE FOR query hint in SQL Server 2005

Also, do you have a regular scheduled index rebuild job for some or all of your indexes?