Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
There can be reasons like Memory configuration across - dev, test and PROD. See my question difference in execution plans on UAT and PROD server - if it helps. Also, worth reading Slow in the Application, Fast in SSMS? –  Kin Jan 27 at 17:28
    
Thanks, I didn't see your question when I originally searched so I'll take a look at that. As in your situation, our prod server has more cpu's and memory than test. Reading the KB prompted me to look at trace flags. I found that test has 1117, 1118, and 2371 all turned on but prod has none. I think a couple of those might be applicable here. –  Chris Jan 27 at 18:48
    
Posting execution plans - both test and prod will help us give better answers. In my case, T2335 did the trick. –  Kin Jan 27 at 19:45
    
I am going to add the flags from my post above (just need a good time to bounce prod). If that doesn't work, I'll try out 2335. I'll post back with the results. –  Chris Jan 28 at 20:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.