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've been working on a software/hardware upgrade from SQL2005 on win 2003 to SQL2008 R2 SP2 on win2008. The new hardware has 48 cores with 128 gig of ram whereas the older server 16 cores with 32Gig ram. We did several batch run and the result just didn't add up. The newer server was slower than the older server?

I've run out of ideas and would like some pointers please.

Thanks

share|improve this question
1  
Did you update statistics on the database after you migrated? Did you set the compatibility level to 100 instead of <= 90? –  Aaron Bertrand Oct 18 '12 at 19:48
    
Parallelism settings? Were indexes rebuilt? At 100% (0) fillfactor? Is this Enterprise Edition - I wouldn't think so since you jumped from 16 to 48 cores; that costs a penthouse. And if it's not, how many cores are allocated to SQL? –  孔夫子 Oct 18 '12 at 19:55
    
You need to find out what "slower" actually means. Start with this DBA.SE thread regarding wait states and see if you find something that stands out. –  Eric Higgins Oct 18 '12 at 20:23
    
Hi Aaron, I've done these. I believe it's the application but I'm finding it difficult to prove. We have 2 of these 48 cores servers and they both perform the same, slower than the old server! –  TST88 Oct 18 '12 at 20:32
    
Hi Richard, I've left the default as it is for the number of cores available for sql –  TST88 Oct 18 '12 at 20:33
show 8 more comments

1 Answer 1

Run the same query on a backup on both systems that matches production as closely as possible. Show us the queries, execution plans, execution times, and perfmon counters. This is the best way to find out what's happening. I'm sure we can see what's going on. During 1 DB migration I had a major networking issue with a firewall pegged at 100% CPU between the web servers and SQL. End user experience was horrible, and NETWORK_IO was the highest wait stat, so maybe it's not SQL Server related.

I recently went from 2x E 4core / 48gb ram to 4x E7 8 core CPUs /256GB ram and saw some interesting results as well.

I had to tweak: -Parallelism threshold and set maximum parallelism setting per query. Check your SELECT * FROM SYS.DM_OS_WAIT_STATS ORDER BY 3 Seeing CXPACKETS way high won't mean you have a parallelism issue, but I found it helpful to increase the threshold and CXPACKETAS fell from #1.

share|improve this answer
add comment

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.