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.

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

share|improve this question

migrated from stackoverflow.com Sep 29 '11 at 12:30

This question came from our site for professional and enthusiast programmers.

3  
Add the query plans please. And difference in versions via SELECT @@VERSION? –  gbn Sep 29 '11 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 '11 at 13:18

3 Answers 3

up vote 4 down vote accepted

Are the two instances indentically configured?

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

share|improve this answer
    
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%' –  Yousui Sep 29 '11 at 11:48
    
Are all other configuration settings also identical? –  Klas Lindbäck Sep 29 '11 at 11:55

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

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

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