Database Cluster CPU choice.

Database Cluster CPU choice.

Post by Sezgi » Wed, 17 Jan 2007 20:37:23

Hi all,

We are considering hardware upgrade.Can SQL Server 2005 Enterprise and
Standard Edition fully take advantage of the Quad Core architecture ?. Quad
Core Intel Xeon X5355 is top of the range. Dual Core 5160 is another choice
worth considering. At the current prices,which one would be a more cost
effective choice.


Database Cluster CPU choice.

Post by Anthony Th » Mon, 22 Jan 2007 04:50:29

his is a pretty general processor architecture question.

Can SS2K and SS2K5 make use of Intel's Hyper-Threading and either Intel's or
AMD's multi-core technologies? Yes, they can. The OS presents any/all of
these as logical processors to SQL Server. However, both provide NUMA
awareness, and SS2K5 provides NUMA optimization for those process
architectures that provide it. This is critical because only Intel's
Itanium and AMD's x64 processor architectures provide this capability.

Each processor architecture provides its own pros and cons depending on the
workload characteristics. As such, a deep understanding of your systems
workload requirements and each processor's architectural capabilities is
necessary before any recommendation could be made.

The fact is that for single-threaded workloads, a single-socket cpu with a
larger on-die cache and higher clock rate will outperform all other

Now, when you start talking about multi-tasking as in desktop/workstation
scenarios or multi-threading applications as in server DBMS or high-end
gaming systems, multi-core/multi-socket presents real multi-tasking and
parallel computing opportunities that are just not possible with sing
processor solutions, no matter how fast.

Whether you use Hyper-Threading technology, multi-core, or a combination of
the two, you can not match the power of SMP multi-socket. Even in the link
that you provided, Intel reports a 50% to 80% improvement of the Quad-Core
over the previous generation Dual-Core products. Wait a minute! It just
doubled the number of cores, but only obtained, at best, an 80% performance
improvement? If you were to double the number of sockets, you would double
the amount of power, and, unfortunately, you would double your cost and
energy consumption as well. This is the advantage multi-core provides over
SMP scalability, not to mention that most software vendors only require you
to license the sockets, not the cores nor the Hyper-Threads.

However, straight SMP architecture also hits a scalability limit: as you add
additional processors to the system (sockets), you increase the amount of
overhead required to manage thread context switches and the associated
memory to cpu loading. Once you get above 8-way solutions, the SMP
controller and cpus start to spend more time moving data on and off the
processors as scheduling requests come in than they do running actual
requested work. Intel's Hyper-Threading Technology was an attempt to
alleviate this congestion. But there are drawbacks associated with its use
as well.

The advantage of Hyper-Threading is that two full thread contexts are loaded
to the CPU at a time. Why it works is based on the probability that the
next scheduled thread will be higher for the last signaled threads than the
others in the queue. In which case, the memory context has already been
loaded to the processor, and as such, reduces the latency inherent in SMP
overhead. However, if that assumption is false, as is the case most often
in OLTP DBMS solutions, the overhead of loading 2 contexts with every
scheduling request can actually double the SMP overhead. For DSS and some
OLAP DBMS solutions, very few context switches, relatively speaking, are
requested anyway; so, again the technology can actually increase the SMP
scheduling overhead.

Now, for multi-core solutions. As was stated above, although their per core
processing power is lower