MAXDOP and HT

Question

Post reply

MAXDOP and HT

PiMané

SSChampion

Points: 13965
More actions
November 18, 2016 at 3:54 am

#315107

Hi,
MAXDOP should be set to the number of real cores a CPU has, or lower if the server has some other stuff running or there are lots of concurrent accesses to SQL (one access should have all the cores to itself).
For this it's recommended to disable HT in the BIOS so only the real cores are exposed to SQL... But sometimes we don't manage the BIOS, just configure SQL.
In a server with 4 cores and HT (8 "cores") if we set MAXDOP to 4 how can we guarantee that SQL only uses the "real" cores and not the HT ones?!
Thanks,
Pedro

If you need to work better, try working less...

Viewing 15 posts - 1 through 15 (of 17 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

These articles are about determining the correct MAXDOP setting:

What value should be used for MAXDOP[/url]

MAXDOP of Confusion (Dear SQL DBA Episode 8)[/url]

Back to your question: Internally SQL will use the most efficient usage of cores and memory. Thus a recommendation is to set the Max Degree of Parallelism to the number of cores that you have in one NUMA node. SQL Server tries to keep parallel plans in one NUMA node during query execution which also improves performance.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

PiMané SSChampion Points: 13965 More actions · Answer 2

I know how to configure MAXDOP but my question is does SQL know what cores to use if HT is enabled?!

I can set MAXDOP to 4 on a 4 core 8 HT CPU... SQL can use 2 real cores and 2 HT, or can't it?

Thanks,

Pedro

If you need to work better, try working less...

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

I was editing my first post with the answer to your question, but you were faster with your reply ;-).

Back to your question: Internally SQL will use the most efficient usage of cores and memory. Thus a recommendation is to set the Max Degree of Parallelism to the number of cores that you have in one NUMA node. SQL Server tries to keep parallel plans in one NUMA node during query execution which also improves performance. (quote from Klaus Asschenbrenner)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

PiMané SSChampion Points: 13965 More actions · Answer 4

Thanks 😀

The best practice is to disable in the BIOS... but unfortunately some clients can't change that configuration or just don't want to change it...

Pedro

If you need to work better, try working less...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

PiMané (11/18/2016)
The best practice is to disable in the BIOS...

Err, maybe back at the time of the first generation of hyperthreading, but not any longer.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PiMané SSChampion Points: 13965 More actions · Answer 6

So SQL is "smart" enough to know what are the "real" cores and the HT ones?

It's not logical to set MAXDOP to 2, for example, on a 4 core 8 HT and SQL use 2 HT cores...

I have a server with a D-1540 CPU (8 physical with 2 logical per physical)...

The DMVs show 1 NUMA with 16 online scheduler count...

Sys info DMV show 16 logical CPU, 16 scheduler and 16 HT ratio with 1 physical CPU...

I have MAXDOP set to 6 since SQL has lots of batches per second...

Shouldn't the scheduler only show 8 since the other 8 are HT?!

Thanks,

Pedro

If you need to work better, try working less...

HanShi SSC-Dedicated Points: 33507 More actions · Answer 7

You are mis-interpreting the relation between processor and hyperthreading. With hyperthreading the physical core acts as multiple cores. To the processes the physical core itself is *not* visible/available on its own.

With Hyper-Threading, a microprocessor's "core" processor can execute two (rather than one) concurrent streams (or thread s) of instructions sent by the operating system. Having two streams of execution units to work on allows more work to be done by the processor during each clock cycle . To the operating system, the Hyper-Threading microprocessor appears to be two separate processors. Because most of today's operating systems (such as Windows and Linux) are capable of dividing their work load among multiple processors (this is called symmetric multiprocessing or SMP ), the operating system simply acts as though the Hyper-Threading processor is a pool of two processors.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

PiMané SSChampion Points: 13965 More actions · Answer 8

HanShi (11/18/2016)
You are mis-interpreting the relation between processor and hyperthreading. With hyperthreading the physical core acts as multiple cores. To the processes the physical core itself is *not* visible/available on its own.
With Hyper-Threading, a microprocessor's "core" processor can execute two (rather than one) concurrent streams (or thread s) of instructions sent by the operating system. Having two streams of execution units to work on allows more work to be done by the processor during each clock cycle . To the operating system, the Hyper-Threading microprocessor appears to be two separate processors. Because most of today's operating systems (such as Windows and Linux) are capable of dividing their work load among multiple processors (this is called symmetric multiprocessing or SMP ), the operating system simply acts as though the Hyper-Threading processor is a pool of two processors.

That's very beautiful in paper but HT doesn't have 100% processor power as a real core has... With luck you can get 30%...

So if SQL takes 60 seconds to process in 1 thread an operation with 2 it could take 30 secs plus the time to join the results...

But if one of the cores is HT then 1 thread takes 30 secs but the other takes 100 secs since it only has 30% performance... The query will actually run slower!!

This is my concern...

Pedro

If you need to work better, try working less...

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

You are worried about the wrong thing. Setting MAXDOP to number of physical cores in a NUMA node is more for attempting to ensure you maximize local memory access and don't have to pay the overhead of stepping over to another CPU's RAM bank.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

PiMané (11/18/2016)
It's not logical to set MAXDOP to 2, for example, on a 4 core 8 HT and SQL use 2 HT cores...

Why not?

Shouldn't the scheduler only show 8 since the other 8 are HT?!

No, because you have 16 cores.

The problems that the first generation of hyperthreading had with SQL wasn't so much about the cores, as about the impact of the hyperthreading on the L1 and, to a lesser extent, the L2 caches. The way the caches are used by the multiple streams of execution has been changed in the newer designs, resulting in much better behaviours.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PiMané SSChampion Points: 13965 More actions · Answer 11

GilaMonster (11/18/2016)
PiMané (11/18/2016)
It's not logical to set MAXDOP to 2, for example, on a 4 core 8 HT and SQL use 2 HT cores...
Why not?

What I meant by not logical is not the value set to 2, that perfectly normal if you have a system with thousands batches per second... The not logical is SQL have 4 "real" cores and 4 HT and use one of each..

HT cores don't have the same performance as real cores have... (I think).

So is it "ok" for SQL to use the HT cores even if they aren't as fast? In the example I gave it can be even slower than using just one "real" core...

Thanks,

Pedro

If you need to work better, try working less...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

PiMané (11/18/2016)
HT cores don't have the same performance as real cores have... (I think).

Maybe do some research and testing before jumping to conclusions?

In the example I gave it can be even slower than using just one "real" core...

Was that a tested example, with benchmarks, or a thought experiment?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PiMané SSChampion Points: 13965 More actions · Answer 13

GilaMonster (11/18/2016)
PiMané (11/18/2016)
HT cores don't have the same performance as real cores have... (I think).
Maybe do some research and testing before jumping to conclusions?

I did... Just don't know if modern cores still behave like this...

https://en.wikipedia.org/wiki/Hyper-threading#Performance_claims

In the example I gave it can be even slower than using just one "real" core...
Was that a tested example, with benchmarks, or a thought experiment?

I actually did a benchmark but with a rather old CPU, not a Xeon, a first generation i5 CPU.

It had 2 physical cores and 4 logical cores... I set MAXDOP to 2 and the query was faster than MAXDOP 4, a simple query that returned data from a huge table...

Is in the modern CPU hyper-threading as fast as physical? Cause a 4 cores CPU was faster than a dual core with HT in P4...

Thanks,

Pedro

If you need to work better, try working less...

Gazareth One Orange Chip Points: 27737 More actions · Answer 14

The 30% extra isn't 100% from the "real" core + 30% from the "HT"/logical core. It's all running on the same physical silicon, performing the task 30% quicker with HT on compared to off. Besides, those numbers are from 2002(!) & a few architectures ago, probably not that relevant today.

With HT on they're _all_ logical cores.

Increasing the MAXDOP beyond a certain point can begin to reduce performance. Usually when it's set to larger than the NUMA node size.

In other cases, when the overhead needed to repartition the data streams overcomes the speed boost from running in parallel, you can see a query slow down as you increase the MAXDOP value.