3

I have 2 SQL Server 2008 instances installed on the same server. They have same table structures with same data. I have a stored procedure deployed on both of them. The stored procedure performs differently on them. It takes 5 seconds on one instance and 50 seconds on the other to execute the procedure. I think the possible reasons for this are: index fragmentation, outdated statistics. I defragmented the indexes and updated the statistics but still no luck. Any ideas on this issue?

Thanks

Improve this question
2
  • 3
    Add the query plans please. And difference in versions via SELECT @@VERSION?
    – gbn
    Sep 29, 2011 at 12:41
  • Do you have the user and TempDB databases on the same/similar drives? If one is on a slower drive/RAID it's possible to have differences. We still need the execution plans for both situations in order to be able to say anything.
    – Marian
    Sep 29, 2011 at 13:18

3 Answers 3

Reset to default
4

Are the two instances indentically configured?

One thing to check is if the instances have the same memory allocation.

Improve this answer
2
  • Yes they have the same memory configuration. I use the following query to check the mem configuration. SELECT * FROM sys.configurations where description like '%mem%'
    – Just a learner
    Sep 29, 2011 at 11:48
  • Are all other configuration settings also identical?
    – Klas Lindbäck
    Sep 29, 2011 at 11:55
4

Odds are the statistics are out of date for one of the instances (or both) which is causing crappy execution plans to be generated.

Improve this answer
2

Use with(recompile) on the stored procedures to ensure they are both starting from scratch when it comes to the execution plans.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.