We recently had an issue with reports running in an integrated SharePoint environment where the report runs fine in development, test and staging, but very slow (times out) in production. Running sp_recompile didn't help. It runs fast in SSMS. So, thinking it could be a parameter sniffing problem, we fixed production by adding local variables to the stored procedure and setting them to the input parameters.
The question I have is: why did we have to do this in just one environment? I'd like to understand what could cause an SSRS report to run so poorly on one server when the exact same report works fine and fast on another.
I have checked configurations on the servers and databases (test is a restore from prod) and they appear to be the same. It's frustrating to migrate from one environment to another without issue just to have it fail when it goes live. Any pointers?
