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?

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 HigginsOct 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!
–
TST88Oct 18 '12 at 20:32

Hi Richard, I've left the default as it is for the number of cores available for sql
–
TST88Oct 18 '12 at 20:33

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 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.