Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Logical CPU Count and Hyperthread Ratio ? Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 12:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:33 AM
Points: 1,066, Visits: 3,029
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
Post #1438183
Posted Wednesday, April 3, 2013 1:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1438551
Posted Thursday, April 4, 2013 1:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:33 AM
Points: 1,066, Visits: 3,029
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

Post #1438663
Posted Thursday, April 4, 2013 5:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1438720
Posted Thursday, April 4, 2013 5:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:33 AM
Points: 1,066, Visits: 3,029
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
Post #1438725
Posted Thursday, April 4, 2013 6:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1438735
Posted Thursday, April 4, 2013 6:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:33 AM
Points: 1,066, Visits: 3,029

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
Post #1438748
Posted Thursday, April 4, 2013 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1438805
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse