Dual Core described any processor with two cores. Core 2 Duo was one particular family that happened to have two. So the question was really brand against description, and both names are long retired. What has not changed is why a database person cares, which is cores, clock speed and what you are paying for.
The Old Answer, Briefly
Dual core meant two cores on one chip. It was a description, and plenty of different processors were dual core.
Core 2 Duo was a product name from one generation of Intel’s Core architecture. Every Core 2 Duo was dual core. Not every dual core was a Core 2 Duo.
Both names are museum pieces now. Server processors are counted in tens of cores. The question still gets asked because the confusion underneath it never went away. People still mix up sockets, cores and threads.
The Three Numbers That Matter
A socket is a physical chip in a slot. A core is an independent processor inside that chip. A thread is what the operating system schedules work onto. With simultaneous multithreading each core presents two.
SQL Server sees threads and calls them schedulers. Licensing counts cores. Those are different numbers and confusing them is expensive.
What Your Server Actually Has
SELECT cpu_count AS logical_cpus,
hyperthread_ratio AS cores_per_socket,
cpu_count / hyperthread_ratio AS sockets,
scheduler_count,
max_workers_count,
physical_memory_kb / 1024 / 1024 AS physical_memory_gb
FROM sys.dm_os_sys_info;The column names are unhelpful and have been for years. cpu_count is logical processors, so cores times threads per core. hyperthread_ratio is not a ratio on modern builds. It is cores per socket.
If you need certainty, read it from the error log at startup, which states the detected processor layout in plain words.
Why It Matters More Than It Used To
Since SQL Server 2012, Standard and Enterprise are licensed per core, not per socket. A four core chip costs twice what a two core chip does.
So buying the biggest processor available is now a budget decision too. Fewer faster cores often beat more slower ones for a database. A single query can only go so wide.
Standard Edition also caps what it uses. It takes the lesser of four sockets or twenty four cores. Run Standard on a forty eight core machine and half the hardware sits idle.
Clock Speed Still Counts
A lot of database work is one thread doing one thing. The compile of a query plan, a single row lookup, a scalar function running per row. None of that gets faster with more cores.
Cores help when many people query at once, or when one large query goes parallel. Clock speed helps everything, including the parts that can never be split.
Given a fixed budget I take fewer faster cores for most workloads. I also check the licence cost before anybody orders anything.
Checking It Is Really Using Them
SELECT scheduler_id, cpu_id, status, is_online, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY scheduler_id;A scheduler marked offline means SQL Server sees that processor but may not use it. Usually the edition cap, or an affinity setting somebody left behind. Worth checking on any server that feels slower than its specification.
The Settings That Go With It
SELECT name, value_in_use, description
FROM sys.configurations
WHERE name IN ('max degree of parallelism', 'cost threshold for parallelism',
'max server memory (MB)', 'max worker threads')
ORDER BY name;Buying cores and leaving the cost threshold for parallelism at its default of 5 is a common pairing. That default was set when a dual core machine was a large server. It sends trivial queries parallel on hardware that has no business doing so.
What I Would Ask Instead
Not which processor is better. Ask how many cores the edition will use. Ask what the licence costs for that count. Ask whether the workload is many small queries or a few large ones. Then ask whether memory is the real limit.
On most servers I look at, memory and storage are the bottleneck and the processors are idle. The brand name on the chip has never once been the answer.
Dual Core is not a specification, it is a sticker that happened to describe two of something.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
9 Comments. Leave new
Hi,
Just a little nitpick. Dual Core means that there’s two CPU cores on a single die (and socket).
Thanks Marko,
As usually I appreciate your comments and they are very accurate.
Kind Regards,
Pinal
Thank you. As I’ve already said couple of times, I like to be pedantic :)
But anyway, this starts to get little confusing. Things used to be simple: either you got single CPU machine or you got multi CPU machine. I think the acronym MPU told that your machine had multiple CPUs.
But now MPU, according to Wikipedia, can mean either multiple cores on a single die/socket or multiple cores on multiple dies/sockets. And now when single die can contain one or more cores plus that motherboard can have multiple sockets, things start to get really confusing.
As a DBA I started to think about processor affinity. Let’s say I have a machine which has two sockets, four cores on each socket. Would it be wise to tell Sql Server to use those four cores on socket 1 on running queries and other four cores on socket 2 on indexing or would it be wiser to mix them? Or is this something that has no affect what so ever?
Unfortunately I don’t have test environment where I could play with these settings freely. Otherwise I could test this myself (and report the results).
Isn’t this sort of information freely available on the internet? As helpful as your posts are, I’d have searched for it rather than ask a MVP.
Same applies to a lot of the questions you seem to get about SQL syntax/features that are documented in Books Online.
John,
You are very correct, there are lots of information are available online and searching them online is sometime much faster to get the data.
However, the hard reality is that we all do receive questions from beginners and new to this area.
This is just my humble attempt to help those who are looking for help.
I have answered many basic such questions. One can also search easily in my blog here : http://blog.sqlauthority.com/search-sqlauthority/
Many thanks for your observation, you are very correct.
Kind Regards,
Pinal
Hi, Pinal. I also got similar results when I searched about this question earlier, I would like to add that;
“Intel’s dual core processors were simply 2 Pentium 3 processors that were fabricated in a single chip. As they refined their product more, they decided to differentiate their second set of processors from the Core Duo and decided to call it Core 2 Duo.”
And We can summaries that;
1. Dual core is the generic name given to processors that have 2 cores on the same chip
2. Core 2 Duo is the second line of dual core processors from Intel
3. Other dual core processors aside from Core 2 Duo are the Core Duo from Intel and the X2 series from AMD
4. All Core 2 Duo processors are dual core but not all dual core processors are Core 2 Duo
Read more: Difference Between Dual Core and Core 2 Duo | Difference Between
Hi Pinal,
Thanks for putting out the article. As developers/Architects it is important to be aware of the hardware aspects of sql server, like what kind of server is your dev,test and prod servers are running on. For example OLTP/OLAP environments could have different hardware server configurations.
Hi Pinal,
We are running server 2003 r2 x64 with sql 2008 Enterprise with 12gb RAM and 1 dual core processor.
We are running a ready heavy query which is using 11.7gb pagefile.
We are aware that we need to improve performance, the question is do we get more RAM or switch to a quad core processor?
Hi all,
First of all thanks to Pinal Dave for this really useful site.
I think this matter is worthy to analyze because of the enormous costs of the several socket servers instead of multiple-core cheap servers of today.
I use today an AMD Phenom II X4BE and an Intel Core2 Duo to test TPM scripts on SQL.
There are significant differences, the first one is that Intel used two PIII related cpu’s with 2x32k L1 and 4 mb of shared cache on L2.
AMD X4 use 2×64 kb L1 per core, 512k L2 and a massive 6 MB L3 shared.
In my humble opinion and using SQL, the cores add only 0.25% of raw performance per core (approximately) on Intel and 0.35 to 0.40% per AMD core (Using a modern OS), It really depends on the cache architecture that benefits clearly AMD because Intel implement a similar model on i3, i5, and i7 processors, but not present in Intel core2 Duo. (Due to cost restrictions I decided to follow the AMD way instead of waiting for the expensive Intel Xeon counterparts).
One of the differences of the lack of performance in many servers was due to the ancient need of AMD CPU drivers (2003 and below). But to this day this is obsolete thinking because AMD support is native to vista/windows 2008 server clearly closing the gap between the two different architectures in performance but not in cost.
I recommend a insightful reading to comment:
HP DL785: ‘The BI Power Server’ by HP
An architect’s review of HP’s newest AMD-based server for Microsoft Business Intelligence Applications
By Derek Comingore Senior BI Consultant Scalability Experts November 19th, 2008
I know the article can be a little biased, but I think that your experiences will be more accurate.