What is the Difference Between Physical Sockets, Physical Cores, and Logical Cores?

Even after many years, I think that there may still be some confusion about how these three terms are used and what they mean in relation to SQL Server hardware selection and SQL Server licensing considerations.

The hierarchy works like this:

• A physical socket on a motherboard where a physical processor fits (used for licensing before SQL Server 2012)

• A physical core within a physical processor (multi-core, used for licensing since SQL Server 2012)

• A logical core within a physical core (Intel hyper-threading or AMD SMT)

Processor History

Back in the prehistoric days of processor technology (around 2001) all Intel and AMD processors had only one physical core. If you wanted multiple threads of execution, you needed additional physical processors, since one socket = one physical processor = one physical core. Back then, the primary way to increase single-threaded performance was to increase the clock speed of the processor. Both Intel and AMD started running into problems with heat dissipation and power consumption as clock speeds approached 4.0 GHz (on air cooling).

In 2002, Intel introduced the first processor with hyper-threading. Hyper-threading creates two “logical processors” within each physical processor core of an actual physical processor, that are both visible to the operating system. Depending on the application, hyper-threading can improve total CPU capacity by anywhere from 5-30%. Hyper-threading does not improve single-threaded performance, it just adds some additional CPU capacity, depending on the workload.

The initial implementation of hyper-threading on the Pentium 4 Netburst architecture did not work as well on many server workloads (such as SQL Server), so the standard advice back then was to disable hyper-threading on database servers. The 2nd generation hyper-threading in the Intel Nehalem and newer processors works much better for SQL Server OLTP workloads, so I always leave it enabled by default.

One modern security consideration regarding Intel hyper-threading is that some recent CPU exploits such as Zombieload target hyper-threading. Zombieload is a speculative execution side-channel attack. It uses Intel Hyperthreading to execute a Microarchitectural Data Sampling (MDS) attack. Because of this some organizations have decided to disable hyper-threading in their servers.

In 2005, AMD introduced their first dual-core processor, the Athlon 64 X2. This processor had two discrete physical cores, which provided better multi-threaded performance than hyper-threading. A single, dual-core processor would have two processor cores visible to Windows. It is important to remember that Windows Server 2008 R2 Task Manager (and some SQL Server DMVs in older versions of SQL Server) cannot easily tell the difference between hyper-threaded logical processors and true dual-core or multi-core processors. Windows Server 2012 Task Manager and newer explicitly show this information as shown in Figure 1.

You can also see core and thread counts of your processor(s) in CPU-Z, as you can see in Figure 2.

In late 2006, Intel introduced the first Core2 Quad, which was a processor with four physical cores (but no hyper-threading). One of these processors would have four cores visible to Windows. Since then, both AMD and Intel have been rapidly increasing the physical core counts of their processors. AMD had the Opteron 63xx processor family which has 16 physical cores in a single physical processor. Intel had the Xeon E7 Family “Westmere-EX”, which had up to ten physical cores, plus 2nd generation hyper-threading, which means that you have a total of 20 logical cores visible to Windows and SQL Server for each physical processor.

The latest AMD EPYC 7002 series “Rome” server processors have up to 64 physical cores plus SMT (so 128 logical cores per socket). The latest Intel Xeon Platinum 8200 series “Cascade Lake-SP” processors have up to 28 physical cores plus hyper-threading (so 56 logical cores per socket).

SQL Server Licensing

Before SQL Server 2012, SQL Server licensing was only concerned with physical processor sockets, not physical cores, or logical cores. Knowing this, you wanted to always buy processors with as many cores as possible in order to maximize your overall processor capacity per processor license. You should also be aware that SQL Server 2008 was limited to 64 logical processors. In order to use more than 64 logical processors, you needed to be running SQL Server 2008 R2 on top of Windows Server 2008 R2, which raised your limit to 256 logical processors.

SQL Server 2008 R2 Enterprise Edition also had a license limit of eight physical processors (which would let you go up to 160 logical processors with eight Intel Westmere-EX processors). If you needed more than eight physical processors, you needed to run SQL Server 2008 R2 Data Center Edition. Microsoft got rid of the Data Center Edition SKU for SQL Server 2012.

With SQL Server 2012, Microsoft completely changed their licensing model compared to previous releases. With SQL Server 2012 Enterprise Edition, in a non-virtualized environment, you must use core-based licensing, which is based on physical cores (not logical cores). Each processor socket must have at least four processor core licenses (even if there are actually only one or two physical cores). This means you needed to be much more thoughtful about which exact processor model you select for your database server. Having lots of physical cores can add up to a very large amount of money for your SQL Server 2012 Enterprise Edition license costs. If you are running SQL Server 2012 Enterprise Edition on top of Windows Server 2012 Standard Edition, you can now have up to 640 logical cores, along with 4TB of RAM in your system.

This new core-based licensing system really penalized AMD Opteron processors, which could have up to 16 physical cores in each processor, but unfortunately, had pretty mediocre single-threaded performance. To try and level the playing field a little bit, Microsoft released something called the SQL Server Core Factor Table, which gave a 25% discount for most AMD Opteron processors that had six or more physical cores. Even with this discount, it was far more expensive to buy your SQL Server 2012 or 2014 core licenses for an AMD Opteron system compared to an equivalent Intel system.

Microsoft continued using the SQL Server Core Factor Table for SQL Server 2014, but then did away with it for SQL Server 2016 and newer. AMD EPYC 7002 series “Rome” processors have competitive single-threaded CPU performance with modern Intel Xeon processors (depending on the exact SKUs), so this is not really an issue anymore.

Modern versions of SQL Server Enterprise Edition can use as many sockets or cores as the operating system it is running on, whether it is Windows Server or Linux. SQL Server 2019 Standard Edition is limited to the lesser of four sockets or 24 physical cores, so make sure you keep that in mind.

Remember that older versions of the sys.dm_os_sys_info DMV cannot tell the difference between physical and logical cores. If you have SQL Server 2016 SP2 or later, then sys.dm_os_sys_info will report complete information about physical and logical cores.