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://sqlserverperformance.wordpress.com/2011/04/05/a-sql-server-hardware-nugget-a-day-day-5/
http://en.wikipedia.org/wiki/Hyper-threading
