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

LivingForSqlServer

I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer

Logical CPU, Physical CPU and hyper threading

Those who are fan of Glenn Berry’s DMV queries would have come across below query at-least once.

-- Hardware information from SQL Server 2008

-- (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 OPTION (RECOMPILE);

And DBAs who are not familiar with hardware will be curious to understand the difference between
Logical and Physical CPU. I am no exception.

Nowadays I am reading lots of Glenn Berry’s hardware related posts. His post makes me bit familiar with hardware terminologies like Sockets, CPU Cores, Hyper threads and Logical CPUs.

Here are some basic but key points (learnt from Glenn Berry):

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

·         CPU Core - Physical core within a physical processor

o   Duel core – 2 Cores in a physical processor

o   Quad core – 4 Cores in a physical processor

·         Hyper threading - Logical cores within a physical core

In simple terms, In a Duel core processor if Multithreading is enabled, Operating system will treat it as 4 logical processors.

But the above query leads to one confusion on hyperthread_ratio. Below link clarifies that.

http://sqlblog.com/blogs/kalen_delaney/archive/2007/12/08/hyperthreaded-or-not.aspx

 

SQL Server Hardware Choices Made Easy eBook by Glenn Berry is an excellent source to learn more about hardware.

 

It’s very important e-book because whoever start their performance tuning work in any version of SQL Server will finally end up with Hardware.

 

Reference:

http://www.sqlservercentral.com/blogs/glennberry/archive/2011/10/18/october-2011-sql-server-2008-diagnostic-information-queries.aspx

http://mvdit.wordpress.com/2010/09/01/difference-between-intel-core-i3-core-i5-and-core-i7-processors/

http://sqlserverperformance.wordpress.com/2011/04/05/a-sql-server-hardware-nugget-a-day-day-5/

http://sqlserverperformance.wordpress.com/2010/10/09/what-are-physical-sockets-physical-cores-and-logical-cores-and-why-does-it-matter/

http://en.wikipedia.org/wiki/Hyper-threading

 

Comments

Posted by Glenn Berry on 31 October 2011

Thanks for the mentions and the kind words.

Posted by Ramkumar on 31 October 2011

Sure Glenn.

You and Paul Randal are my role models and you both are the source of most of my learning :-)

Posted by . on 28 November 2011

I heard that the hyperthread_ratio in this sys.dm_os_sys_info system view is not always accurate & reliable in using in ultimately trying to calculate the [Physical CPU Count], if this is correct how would one get the [Physical CPU Count] if one is unable to physically get to it and is located remotely?

Leave a Comment

Please register or log in to leave a comment.