21 January,2013 by Jack Vamvas
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
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 How to find CurrentClockSpeed and MaxClockSpeed using Powershell Get-WmiObject win32_processor
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