Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logical CPU Count and Hyperthread Ratio ?


Logical CPU Count and Hyperthread Ratio ?

Author
Message
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465
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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465

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
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
See Chapter 3 of this book:

Troubleshooting SQL Server: A Guide for the Accidental DBA by Jonathan Kehayias

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search