Avoid SSRS Parameter Sniffing and Speed up your reports!
So we were investigating an issue on an SSRS report that calls out to a stored procedure. If we run the stored procedure directly from a query window, it will return in under 1 second. However, the same query run from an SSRS report takes up to 5+ minutes to complete.
We have found a solution and it did turn out to be related to the parameters. SQL Server was producing a convoluted execution plan when executed from the SSRS report due to SSRS parameter sniffing. The workaround was to declare variables inside of the stored procedure and assign the incoming parameters to the variables. Then the query used the variables rather than the parameters. This caused the query to perform consistently whether called from SQL Server Manager or through the SSRS report.
Another possible solution is to add option(recompile) to the end of the sp. We have not tried it yet, but I think the the first option is the best solution.