Logical CPU Count and Hyperthread Ratio ?

Question

Logical CPU Count and Hyperthread Ratio ?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
April 3, 2013 at 12:53 am

#292035

Hi,
The query output as below
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_timeFROM sys.dm_os_sys_info OPTION (RECOMPILE);
Logical CPU Count - 16
Hyperthread Ratio - 8
Physical CPU Count - 2
Physical Memory (MB) - 32756
Hyperthread Technology is create two logical processor on actual physical processor, So server has 2 Physical CPU, it means Dual core processor. Hyperthread Ratio is 8, 8*2=16 logical processor.
I want to know, Does Logical CPU Count and Hyperthread Ratio should be same numbers?
thanks
ananda

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

Hyperthread Technology is create two logical processor on actual physical processor, So server has 2 Physical CPU, it means Dual core processor. Hyperthread Ratio is 8, 8*2=16 logical processor.

It means your server has two physical CPUs, i.e. two sockets.

I want to know, Does Logical CPU Count and Hyperthread Ratio should be same numbers?

If you have one physical CPU, i.e. one socket, yes, they'll be the same. If you have more than one physical CPU then they'll be different.

Note that hyperthread_ratio does not differentiate between a core and a Hyperthreaded CPU. In your case, with only the info from this query, we cannot know if you have 2 physical CPUs with 8 cores each, or 2 physical CPUs with 4 cores each plus Hyperthreading.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thanks for your reply...

server have 2 Intel Quad Core CPU 3.6 GHz, so 4 core multi processors and 2 scokets..

How it enable hyperhtread Ratio and Logical CPU both are same number? currently both are display differnt values..

thanks

ananda

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

So 2 physical CPUs x 4 cores each x 2 because of Hyperthreading

= 16 logical CPUs according to SQL Server

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

I got it.. according to SQL output 16 Logical CPU...

Pl. correct me if am wrong direction.

For the performance basis for SQL SERVER parallel processing should be equal number as blow,

Logical CPU Count - 16

Hyperthread Ratio – 16

As per query output, currently display as below

Logical CPU Count - 16

Hyperthread Ratio - 8

how it is enable at HW level or SQL side to make Hyperthread Ratio = 16?

thnaks

ananda

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

ananda.murugesan (4/4/2013)
I got it.. according to SQL output 16 Logical CPU...
Pl. correct me if am wrong direction.
For the performance basis for SQL SERVER parallel processing should be equal number as blow,
Logical CPU Count - 16
Hyperthread Ratio – 16
As per query output, currently display as below
Logical CPU Count - 16
Hyperthread Ratio - 8
how it is enable at HW level or SQL side to make Hyperthread Ratio = 16?
thnaks
ananda

You won't be ever be able to get your Hyperthread Ratio to 16 on the hardware you described. Each of your CPUs has 4 cores + Hyperthreading which means 8 logical CPUs per physical CPU, or a ratio of 8:1 logical to physical. That is a function of the hardware you are running. Now, you could get your Hyperthread Ratio down to 4 if you disable Hyperthreading which can usually be done by changing a setting in the BIOS. Some people opt to disable Hyperthreading since it has less of a benefit with SQL Server than in many other applications and can even harm performance in some cases. Also consider that the name of the column hyperthread_ratio is misleading and was likely named that way prior to multi-core processors being mainstream in commodity hardware.

At minimum I would recommend checking the setting for 'max degree of parallelism' and making sure that is set to no more than 8, which is the number of physical cores in your machine.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

At minimum I would recommend checking the setting for 'max degree of parallelism' and making sure that is set to no more than 8, which is the number of physical cores in your machine.

Thank you for cleared me doubts.. For the CPU Related points..

Max Degree of Parallelism – currently showing values is “0” by default values. It means using all the available Logical CPU for SQL Transaction..

If I have set values 8, SQL Transaction using only 8 logical processor, in which situation we should change the values Max Degree of Parallelism

thanks

ananda

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

See Chapter 3 of this book:

Troubleshooting SQL Server: A Guide for the Accidental DBA by Jonathan Kehayias[/url]

Specifically read the sections discussing the 'max degree of parallelism' and 'cost threshold for parallelism' server settings.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato