High performance machine executes SQL queries slower than a normal one?

Question

We have an 8 CPU 2.5GHz machine with 8 GB of RAM than executes SQL quries in slower fashion than a dual core 2.19 GHz with 4GB of RAM?

Why is this the case, given that Microsoft SQL server 2000 is installed on both machines

Answer 1

Just check these links to indicate where the bottleneck is situated

http://www.brentozar.com/sql/

I think the disk layout and the location where which SQL server database files are causing the trouble.

Answer 2

Also, you may have different settings of SQL Server (memory assignments and AWE memory, threads, maximum query memory, processor affinity, priority boost).

Answer 3

Check the execution plans for the same query on both machines and, if possible, post it here.

Most probably it will be the case.

Answer 4

Our solution for multicore servers (our app executes many very complex queries, which tend to create many threads and these start to interlock and even deadlock sometimes):

sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'max degree of parallelism', 1
reconfigure

This is not ideal solution, but we haven't noticed any performance loss for other actions.

Of course you should optimize disk layout too and sometimes limit SQL server memory for 64bit server.

Answer 5

Keep in mind that just be cause one machine has more CPUs running at a higher clock speed and memory than another, it's not necessarily going to solve a given problem faster than another.

Though you don't provide details, it's possible that the 8-CPU machine has 8 sockets, each with a single-core CPU (say, a P4-era Xeon) and 1 GB of local (say RDRAM) RAM. The second machine is a modern Core 2 Duo with 4GB of DDR2 RAM.

While each CPU in machine #1 has a higher individual frequency, the netburst architecture is much slower clock-for-clock than the Core 2 architecture. Additionally, if you have a light CPU load, but memory-intensive load that doesn't fit in the 1GB local to the CPU on the first machine, your memory accesses may be much more expensive on the first machine (as they have to happen via the other CPUs). Additionally, the DDR2 on the Core 2 machine is much quicker than the RDRAM in the Xeon.

CPU frequency and total memory aren't everything -- the CPU architecture, Memory types, and CPU and memory hierarchy also matter.

Of course, it may be a much simpler answer as the other answers suggest -- SQL Server tripping over itself trying to parallelize the query.