1

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

Tim Westover
  • 133
  • 3
  • Is the DB using the same storage it was before (i.e. a SAN), or did you move it to local storage on the new server? – 1.618 Apr 30 '13 at 13:24
  • Have you configured `MAXDOP` to the number of cores / CPU node? With the bigger machine, and the default MAXDOP setting of 0, parallelized queries will be much more parallel and will be consuming far memory instead of local memory. This has the potential to slow the new machine quite a lot. We talked about MAXDOP extensively over at http://dba.stackoverflow.com on this question: http://dba.stackexchange.com/questions/36522/what-is-a-good-repeatable-way-to-calculate-maxdop-on-sql-server – Hannah Vernon Apr 30 '13 at 14:07
  • Ah, I like MAXDOP. I will check that out. – Tim Westover Apr 30 '13 at 14:43
  • Both servers use local storage, RAID 10 on traditional hard drives (not SSDs). The new server puts tempdb on its own set of drives in RAID 5, separate from the OS drives and the data / log drives. – Tim Westover Apr 30 '13 at 14:44
  • 1
    using RAID 5 for tempdb might not be the best option since RAID 5 is pretty slow for write operations. How was the previous server storage setup ? – Stephane Apr 30 '13 at 14:48
  • The old server had the tempdb on RAID 10 but the same physical drive set as the databases. – Tim Westover Apr 30 '13 at 16:42
  • Are you sure about this statement: "If there are any missing indexes (I don't think there are) they affect the old and new servers equally." I would start with the assumption that some of the indexes did not transfer with the restore, and take steps to make sure all of the indexes present in the source DB are actually present in the target DB too. – TTT Jul 09 '13 at 22:08

0 Answers0