10

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.

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

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

10

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.

4

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

3

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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