• 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