• Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
    Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.

simple question (i hope) - HyperThreading & Windows Server 2k3

D

djshelto

2[H]4U
2FA
Joined
Nov 11, 2000
Messages
2,305
Hi, I'm hoping someone can answer this quick for me...i've tried searching here and googling, but my brain is fried so i may be wording the search wrong.

I'm working a server at work, it has 4 Xeons with HT enabled. we're getting a ton of parallelism locks in sql server, and i suspect the problem could be HT.

i'm testing a few different things (short of turning off HT, which requires the netadmin's help), but i can't figure out how to determine what order the logical CPUs are being seen in windows.

is there a simple way to find out which of these setups are being used?
setup 1:
Physical -> Logical
0 -> 0
0 -> 1
1 -> 2
1 -> 3
2 -> 4
2 -> 5
3 -> 6
3 -> 7

setup 2:
0 -> 0
1 -> 1
2 -> 2
3 -> 3
0 -> 4
1 -> 5
2 -> 6
3 -> 7


please help!!!
tia
dennis
 
Usually it's

CPU 0 Physical/Logical

CPU1 Physical/Logical

It alternates between them.
 
I remember reading that it was the way I outlined it above, but I don't know if you can actually tell for sure.

I hand't read that they could map differently either. It was so long ago when I built a dual Xeon with HT for a customer and for some reason I had some problem that required me to look into it at that time. I haven't messed with it in so long I am not sure.
 
djshelto said:
...I'm working a server at work, it has 4 Xeons with HT enabled. we're getting a ton of parallelism locks in sql server, and i suspect the problem could be HT...


please help!!!
tia
dennis
Click to expand...

I wouldn't blame HT for this one.
HT is not doing anything that having multiple cpus wouldn't exaserbate.

The locks are due to poor transaction demarcations in the accessing application(s).

Is there a pattern in your need to quiese the DB?
 
I suspect HT just because I know in some instances it can cause these problems (CXPACKET waits) and it's the easiest thing to fix if it's the problem (occam's razor)

I've noticed the problem with 2 particular queries so far.

1 is a join using "NOT IN", which i know is horribly inefficient but i can't take it out just yet. plus, it only occurs in certain, seemingly random circumstances.

If I let SQL Server use more than 1 CPU, it takes 2-15 minutes to finish the query (joining 120k rows with 60k rows on a single field). If i force it to use 1 CPU, it takes less than 2 seconds.

the 2nd appearance is an update statement in a procedure that's building a table from a series of individual counts. the same update statement is used about 12 times, in slightly different combinations, and these 12 are run for each of about 700 reports. 1 of the 12 on 1 of the 700 (so far) takes about 4 minutes to complete. all the rest take less than a second.

Again, if I forcibly limit the query to 1 CPU, it runs in it's normal 1/2 second or so.


ultimately, we're bringing in a DBA in the near future...i just want to find out what order my CPUs show up in so i can determine for myself if HT is a problem.
 
Having done this kind of debugging myself (I tend more towards the DBA side of development on both SQL Server and Oracle with a bit of DB2 for good measure) I would say that SQLServer is making some bad decisions. The likely problem is either bad SQL statements or bad optimizer paths.

Both are easy to fix in the short term once you find them. Finding them and ensuring that the fix stays fixed in the long term can be a bit of a trick.

From what I know of HT you could very well have these same problems with it turned off. It may not happen until you put a significant load on the system, but fixing the root of the problem will be better than simply turning off HT.
 
djshelto said:
If I let SQL Server use more than 1 CPU, it takes 2-15 minutes to finish the query (joining 120k rows with 60k rows on a single field). If i force it to use 1 CPU, it takes less than 2 seconds.
Click to expand...

That should tell you HT is not the problem.
Again HT is not doing anything that having multiple cpus won't make ever worst.
There is a threading issue somewhere and you will still that issue with HT off if you have more than 1 cpu.

klbennet said:
From what I know of HT you could very well have these same problems with it turned off. It may not happen until you put a significant load on the system, but fixing the root of the problem will be better than simply turning off HT.
Click to expand...

Exactly.

OP, please revise your transaction settings.
 
again, i can't eliminate HT because i can't be sure that by limiting SQL to, say, 2 cpu's that it's not trying to use to logical units on the same physical processor.

odds are you're right, it's an MP problem, not HT, but i need to eliminate this, because there *are* cases where HT is the problem, and the only way to fix it is by disabling HT.

all i'm looking for is a method for determining the layout of logical cpus vs. physical cpus. we'll be bringing a DBA in to handle the other things.

can anyone answer the question originally asked?
 
Why don't you just turn off HT? It should be a bios setting. The system would just run in SMP mode with no HT stuff involved.

Maybe I missed something, but that would seem to be the easiest solution to rule out HT. Regardless of what you do to pin SQL to specific processors there will still be some HT magic involved if you don't just turn it off.
 
that's what i'm going to end up doing...i was hoping there was a simple way of finding out the processor order just so i wouldn't have to have the IT department mess with the server...they borked the server upgrading ram earlier this week, so i'm a little leery at times

thanx for the advice though
 
Well, from your first post it seems like 0 and 7 are always the same.
Why don't you try using only those two?
 
doh

because i was waaay too tired to spot that...thank you, i'll pursue that this weekend
 
I'll ask around at work about this KB article. It seems quite misguided, and perhaps it should be pulled. This thread makes the FUD it's causing very apparent.

SQL Server doesn't rely on the OS scheduler as much as most applications in Windows. As a result, it has some configuration options that describe the operating environment to it. I don't think that the logical-to-physical mapping that the article expresses is exposed to the user; it certainly isn't documented, which is why I'm surprised to see it described here.

The article actually answers the question for you, but it's really opaque about it. To figure out which mapping is happening, you can:

1) Set the affinity mask to 00000011.
2) Restart SQL Server
3) Load the server
4) Observe which processors are being used.

If you see that Physical processor 0 (and its Hyperthread) is the only one that's busy, you know you have the mapping in Table #1. If you see some other combination is busy, then you know you have a different mapping. If you're using Table #2, then you should be able to set SQL Server's affinity mask to 00010001 and witness activity on only processor 0 (and its hyperthread).

Before we go any further, I'd like you to explain what the exact problem is that you're seeing and what you want to solve. Your original note says you're seeing "a ton of parallelism locks", and I'd like to know what that term means to you. Where do you see these "parallelism locks", and why did you conclude that hyperthreading is causing them? Do you mean that you're seeing Parallelism operators in the plans for the queries you're executing? Or do you mean that you're seeing an unexpectedly high number of lock objects in the system DMVs, or in perfrmon?

And why do you think this mapping matters?

Which version of SQL Server are you using on which OS, by the way?

If you're running Windows 2003 Server, there are system APIs which will dump the mapping. But that will just bring us back to the question of which tool you're using to look at the processor usage, and then why you think that scheduling is a problem.

If you're running Windows 2000 Server, I'd consider turning HT off altogether, as Windows 2000 Server isn't HT-aware and won't always schedule correctly. It might schedule well, or it might not; it depends on your workload and you'll have no choice but to test it both ways.

Are you using fibers?
 
You must log in or register to reply here.
Back
Top