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.

We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the new server is slightly worse than the old, slower server.

In testing, our ASP.NET application runs 10 - 20% slower. Running individual expensive queries with STATISTICS IO and STATISTICS TIME shows 10 - 20% greater elapsed time on the new server. SQL Query Profile shows higher CPU usage on expensive queries.

Task Manager on the new server shows sqlserver.exe is consuming 22 GB of RAM, but the CPU values always stay very low.

I've updated all statistics, rebuilt or reorganized indexes, etc. Execution plans should be stored on the new server at this point, given the amount of testing I've done. If there are any missing indexes (I don't think there are) they affect the old and new servers equally. New has a restored backup of the same data on the old.

I'd expected that the performance on the new server would be better, but of more concern is load. If the old server is performing better even under load, then what will happen when this new, slightly worse server has to take that load?

What else could I be missing here?

EDIT: MAXDOP set to 6.

Old server has OS, databases, and tempdb's on the same physical drives (RAID 10). Total of 4 15k 3 Gb/s 3.5 inch SAS. New server has three drive sets: OS on RAID 1, database on RAID 10, tempdb on RAID 5. Total of 9 15K 6 Gb/s 2.5 Inch SAS.

Old server has 1 x Intel Xeon E5620 2.40 GHz Quad-Core 8 Threads (w H/T). New server has 2 x Intel Xeon E5-2640 2.5 GHz Six -Core 12 Threads (w H/T).

EDIT 2: Here's the final analysis:

The power plan was on balanced, not high performance. Switched that over.

Tempdb was on a RAID 5, not RAID 10. Added another HD to create two physically distinct RAID 10 configs, one for tempdb and one for everything else.

Excluded SQL-related files (mdf, ldf, ndf, bak) from virus scanning.

Rebuilt all indexes following the move to the new server. They were very fragmented -- possibly as a result of backup, copy, restore?

And I realized that the processor jump was not that big. The queries aren't going to execute that much faster, but with more processors, more cores, more RAM, we'll be more scalable.

share|improve this question

migrated from stackoverflow.com Apr 30 '13 at 13:37

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

3  
Have you done any baseline comparisons? Do testing of the machines just with Windows. Maybe it's something is configured in Windows differently. –  Jeff B Apr 30 '13 at 13:16
5  
You should mention your disk/array/SAN setup. Swap, Tempdb, etc on which drives with specs, and SQL config. –  Tim Lehner Apr 30 '13 at 13:52
4  
When you are changing hardware, fist check point is to see -- the windows power plan configuration -- if it is set to balanced or high-performance. HIGH PERFORMANCE is the recommended mode and changing it does not require a reboot as well. This might be somewhat related to what you are facing .. brentozar.com/archive/2010/10/… –  Kin Apr 30 '13 at 13:56
3  
PREEMPTIVE_OS* waits indicate that SQL is being held up by external OS operations. I would investigate what else is running on the server other than SQL. I'd also look at how your FT indexes are being used and managed based on the FT rowlock wait. –  Mike Fal Apr 30 '13 at 16:51
4  
Any antivirus solution on the new machine? Check that it's disabled for the mdf/ldf files and for the sql server processes. Is there anything else running on that machine? A Reporting Server, Analysis Services, replication? –  Marian Apr 30 '13 at 20:55
show 10 more comments

3 Answers 3

up vote 10 down vote accepted

Raid 5 is slower than raid 10, especially for write-heavy workloads. As such, it is not generally recommended for SQL server and certainly not for tempdb. This alone could easily explain the performance difference.

My recommendation would be to move tempdb to raid 10.

share|improve this answer
2  
This was the eventual solution that seemed to help the most. Thanks! –  Tim Westover May 8 '13 at 21:08
add comment

Well, you say nothing about your hard disks and the number of tempdb files you have. There is a general recommendation that nr of tempdbs = number of cores up to 32, there is also a switch to throw to ensure that the temp dbs are used equally.

However indepth: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx also have you changed the packing for tables and indexes during the migration? Backup and restore may end up defaulting to a different padding on indexes (including clustered ones)

share|improve this answer
add comment

This is a very general problem, so it's hard to give a specific advise. But, if I were in this situation, I would start from the basics, check the most expensive queries. What functionality is taking longer? What is consuming most time what you run queries with statistics time? Once you narrow your focus a bit, you can compare things with the old server. Also, something to check, is to make sure that both servers are on the same patch level (SQL and Windows).

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.