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?

  • 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 '14 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 '14 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 '14 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 '14 at 20:22

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.