Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

What are Physical Sockets, Physical Cores, and Logical Cores and Why Does it matter?

I witnessed a confused conversation on Twitter today where someone was talking about how he uses the terms sockets and cores interchangeably, since everyone else does, or words to that effect. This made me think that there may be some widespread confusion about how these terms are used and what they mean in relation to SQL Server hardware selection and SQL Server licensing considerations.

The hierarchy works like this:

Physical socket on a motherboard where a physical processor fits (used for licensing)

Physical core within a physical processor (multi-core)

Logical core within a physical core (hyper-threading)

Back in prehistoric days (around 2001) all Intel and AMD processors had but a single 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.0GHz (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 visible to the operating system. Depending on the application, hyper-threading can improve performance by anywhere from 5-30%. 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 was to disable hyper-threading on database servers.

In 2005, AMD introduced the 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 (and SQL Server) cannot tell the difference between hyper-threaded logical processors and true dual-core or multi-core processors.

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 has a processor called the Opteron 61xx “Magny-Cours” which has 12 physical cores in a single physical processor. Intel has the Xeon 75xx “Nehalem-EX”, which has eight physical cores, plus 2nd generation hyper-threading, which means that you have a total of 16 cores visible to Windows and SQL Server.

In late 2010/early 2011, AMD will release the “Bulldozer” processor, which will have 16 physical cores per physical processor, while Intel is scheduled to release the “Westmere-EX”, which will have 10 physical cores, plus 2nd generation hyper-threading, which means that you will have a total of 20 cores visible to Windows per physical processor. The 2nd generation hyper-threading in the Intel Nehalem and Westmere works much better for SQL Server OLTP workloads, so I always leave it enabled.

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

Finally, you should be aware that SQL Server 2008 R2 Enterprise Edition has 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 need more than eight physical processors, you will need to run SQL Server 2008 R2 Data Center Edition.

Remember that Windows and SQL Server cannot tell the difference between physical and logical cores. Running the query below will tell you how many logical cores are visible and how many physical CPUs you have.

-- Hardware information from SQL Server 2008 and 2008 R2 
-- (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info;

Comments

Posted by Hugo Shebbeare on 9 October 2010

download.microsoft.com/.../SQLServer2008_LicensingGuide.pdf

According to the licensing guide they are not only concerned with physical cores.  The guide is sixty-four pages long, but you'll find that there are many scenarios where they discuss pricing based on logical cores used and even HyperThreading.

I'll follow up with this comment later this week, once I have caught up on the guide myself even...

Posted by Glenn Berry on 10 October 2010

Yeah, I tend to think in terms of regular, non-virtualized SQL Server instances. I'll have to take a look at that guide myself.

Posted by Andreas Erson on 18 October 2010

Intel actually announced their Pentium D three days before AMD announced the Opteron 200/800-series and Athlon 64 X2. Intels Pentium D was immediately available and from AMD only the Opteron 800-series was immediately available.

Although AMD for sure had the best implementation.

www.intel.com/.../20050418comp.htm

www.amd.com/.../Press_Release_97108.aspx

Posted by andreas.erson on 18 October 2010

Microsoft has also released a quick reference licensing guide (9 pages) for SQL Server 2008 R2:

download.microsoft.com/.../SQL2008R2_LicensingQuickReference-updated.pdf

Posted by Hugo Shebbeare on 18 October 2010

Thanks Andreas, Page three has the formula in the short guide, but really the long guide needs to be read to understand this complicated virtualisation method.

I found last spring that taking the time to calculate CAL by Device saved us mucho dinero.

Buenas noches.

Leave a Comment

Please register or log in to leave a comment.