Detailing CPU  specifications for a database server  is a rough science. Many factors influence decisions regarding CPU processor speed and CPU cores. Workload , licensing, type of activity and budget are some of the variables in decisions. What’s better : more processor speed or more CPU cores? The answer is not straightforward

Different demands on the hardware are placed depending on the type and level of workload. It is important to specify the workload. What types of queries are running? Are they short fast transactions  or long running queries with multiple joins ? Know your workload

 In general , and there are many variants of the classic breakdown of OLTP v Datawarehouse

OLTP workload gets benefit from :

Single threaded performance

IO subsystem

Fast write performance

Datawarehouse workload gets benefit from :

Usage of Parallelism is assisted by higher number of cores. Read more on SQL Server - Waitstats CXPACKET wait type and how to reduce it

Large amount of RAM – more data in buffer, therefore doesn’t need to access disk as regularly

Very little write activity

Other factors related to CPU –

1) CPU cache  has an impact on read/writes to ram.

2) the shared cache used by two independent cores can compensate for  slower clock rate, which can potentially have better results then a faster clock speed single core

3) Is CPU throttling turned on and other power saving

4) Keep in mind increased RAM = increased CPU usage and less physical IO – as buffer pool is more utilised

5) Often major IO waits will make CPU usage look low – but in fact , it’s waiting for disk IO.

6) Get to know your hardware vendor and experts withing your organisation. Specifying CPU can be costly as it also has an impact on SQL Server licensing costs

Based on my experience the order of priority : disk spindles (iops) ,physical memory , then CPU.

SQL Server will utilise all the cores. SQL Server run one scheduler per CPU\core and many sql worker threads per core – this allows multiple batches to run simultaneously on separate worker threads. Adding up the power of all cores can add up to more than a faster single processor, but it depends on how you are using the CPU

 Read More

 CPU Count - SQL Server DBA

Virtualization – and database servers - SQL Server DBA

SOS_SCHEDULER_YIELD and how to reduce it - SQL Server DBA

Affinity Mask on SQL Server 2008 R2 - SQL Server DBA