Hyperthreaded CPU's

Hi,
This is a follow-up to Ken's earlier Option (MAXDOP 1) thread. We have a
server with 3 hyperthreaded CPUs that will show as 6 CPU's to SQL2000 SP3A.
I am lead to believe that logical CPU's 0, 2 and 4 are the physical CPU's.
If I uncheck CPU's 1, 3 and 5 but keep the Use All Available Processors
checked I am restricting SQL to only using the 3 physical processors and
reducing the horsepower available to SQL to half. Is this statement true?
Thanks
Chris> I am lead to believe that logical CPU's 0, 2 and 4 are the physical CPU's.
This isn't really how HT is working. A physical processor (lets call it "cor
e" from now on, to avoid
confusion if we have multi-core sockets) exposes two logical processors. You
can't say that one of
those logical processors is more physical than the other.
Also, Intel's recommendation for the BIOS/HW manufacturer is as below. Say w
e have 4 core:
Processor 0, as seen by OS: Core 0, logical processor 0
Processor 1, as seen by OS: Core 1, logical processor 0
Processor 2, as seen by OS: Core 2, logical processor 0
Processor 3, as seen by OS: Core 3, logical processor 0
Processor 4, as seen by OS: Core 0, logical processor 1
Processor 5, as seen by OS: Core 1, logical processor 1
Processor 6, as seen by OS: Core 2, logical processor 1
Processor 7, as seen by OS: Core 3, logical processor 1
So, if you want to specify that only one logical processor per core should b
e used by SQL Server,
you should tell SQL Server to use processors 0-3. However, disabling HT at t
he BIOS level might be a
better option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23VpU%237pgHHA.392@.TK2MSFTNGP06.phx.gbl...
> Hi,
> This is a follow-up to Ken's earlier Option (MAXDOP 1) thread. We have a s
erver with 3
> hyperthreaded CPUs that will show as 6 CPU's to SQL2000 SP3A. I am lead to
believe that logical
> CPU's 0, 2 and 4 are the physical CPU's. If I uncheck CPU's 1, 3 and 5 but
keep the Use All
> Available Processors checked I am restricting SQL to only using the 3 phys
ical processors and
> reducing the horsepower available to SQL to half. Is this statement true?
> Thanks
> Chris
>|||Tibor,
We have reason to believe that we are following Processor mapping table 2 as
described in: - http://support.microsoft.com/default.aspx/kb/322385
In the thread I mentioned earlier Andrew J Kelly gave a very good
description of what could be happening.
My question was more that we cannot have our cake and eat it too! If we
either change the affinity mask by using the GUI or sp_configure then we are
reducing the number of logical processors available to SQL by half even
though Windows will still see all logical processors. Disabling in the BIOS
would also reduce the logical processors available to Windows in half. I am
having a hard time convincing developers that hyperthreading may be causing
more performance problems than it could be improving by having more logical
processors.
Thanks for your speedy reply.
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eXArpGqgHHA.596@.TK2MSFTNGP06.phx.gbl...
> This isn't really how HT is working. A physical processor (lets call it
> "core" from now on, to avoid confusion if we have multi-core sockets)
> exposes two logical processors. You can't say that one of those logical
> processors is more physical than the other.
> Also, Intel's recommendation for the BIOS/HW manufacturer is as below. Say
> we have 4 core:
> Processor 0, as seen by OS: Core 0, logical processor 0
> Processor 1, as seen by OS: Core 1, logical processor 0
> Processor 2, as seen by OS: Core 2, logical processor 0
> Processor 3, as seen by OS: Core 3, logical processor 0
> Processor 4, as seen by OS: Core 0, logical processor 1
> Processor 5, as seen by OS: Core 1, logical processor 1
> Processor 6, as seen by OS: Core 2, logical processor 1
> Processor 7, as seen by OS: Core 3, logical processor 1
> So, if you want to specify that only one logical processor per core should
> be used by SQL Server, you should tell SQL Server to use processors 0-3.
> However, disabling HT at the BIOS level might be a better option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23VpU%237pgHHA.392@.TK2MSFTNGP06.phx.gbl...
>|||Chris,
Some systems follow the rules that Tibor mentioned and some don't in regards
to how they number their physical and logical processors. There are some
utilities out there on the net that can interrogate your system to see which
is which. But once you determine for sure which ones are the physical ones
and which are only logical you have several options. You can disable the
logical ones via the affinity mask and that will prevent SQL Server from
using them but not the OS or any other apps. If you are only running SQL
Server you are probably OK just disabling them with the affinity mask. But
if you really want to see for sure if HT is helping or hurting I really
suggest you turn it off at the BIOS level so there is no mistaking the
effects. Personally I wish they never invented HT processors and everything
was dual core. While I have seen some cases where HT made a positive
difference I have seen far too many where its behavior was misunderstood and
it ended up hurting more than helping. I wouldn't recommend anyone buy HT
enable processors for SQL Server going forward.
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OOrYfVqgHHA.3412@.TK2MSFTNGP02.phx.gbl...
> Tibor,
> We have reason to believe that we are following Processor mapping table 2
> as described in: - http://support.microsoft.com/default.aspx/kb/322385
> In the thread I mentioned earlier Andrew J Kelly gave a very good
> description of what could be happening.
> My question was more that we cannot have our cake and eat it too! If we
> either change the affinity mask by using the GUI or sp_configure then we
> are reducing the number of logical processors available to SQL by half
> even though Windows will still see all logical processors. Disabling in
> the BIOS would also reduce the logical processors available to Windows in
> half. I am having a hard time convincing developers that hyperthreading
> may be causing more performance problems than it could be improving by
> having more logical processors.
> Thanks for your speedy reply.
> Chris
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eXArpGqgHHA.596@.TK2MSFTNGP06.phx.gbl...
>|||> We have reason to believe that we are following Processor mapping table 2 as described in:
-
> http://support.microsoft.com/default.aspx/kb/322385
Ahh, thanks for updating my knowledge on this, I was only aware of table 1,
as the recommendation
from Intel.
I fully concur with Andrew to verify how the layout, using a utility before
experimenting with
affinity mask, and also do a load test to see whether disabling HT at the BI
OS level won't be the
best after all.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OOrYfVqgHHA.3412@.TK2MSFTNGP02.phx.gbl...
> Tibor,
> We have reason to believe that we are following Processor mapping table 2
as described in: -
> http://support.microsoft.com/default.aspx/kb/322385
> In the thread I mentioned earlier Andrew J Kelly gave a very good descript
ion of what could be
> happening.
> My question was more that we cannot have our cake and eat it too! If we ei
ther change the affinity
> mask by using the GUI or sp_configure then we are reducing the number of l
ogical processors
> available to SQL by half even though Windows will still see all logical pr
ocessors. Disabling in
> the BIOS would also reduce the logical processors available to Windows in
half. I am having a hard
> time convincing developers that hyperthreading may be causing more perform
ance problems than it
> could be improving by having more logical processors.
> Thanks for your speedy reply.
> Chris
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eXArpGqgHHA.596@.TK2MSFTNGP06.phx.gbl...
>|||Andrew,
Some weeks ago we moved a highly I/O intense SQL application off of an SQL
clustered server to its own SQL cluster because it could not live with
another SQL application. After some significant tuning of the code, both
apps, and dropping of old data we are about to put them back together again.
The clustered SQL server has 3 hyperthreaded CPU's with hyperthreading
switched on and available to SQL2000 SP3A build 818. I had read that
hyperthreading was not the best for SQL2000 and wanted to see if notable SQL
experts would comment. We did see some CXPACKET waits before and from what
you are saying if we see them again disabling hyperthreading could be the
best action rather than using the affinity mask.
Thanks so much for your valued opinion.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23AFJZsqgHHA.4140@.TK2MSFTNGP05.phx.gbl...
> Chris,
> Some systems follow the rules that Tibor mentioned and some don't in
> regards to how they number their physical and logical processors. There
> are some utilities out there on the net that can interrogate your system
> to see which is which. But once you determine for sure which ones are the
> physical ones and which are only logical you have several options. You can
> disable the logical ones via the affinity mask and that will prevent SQL
> Server from using them but not the OS or any other apps. If you are only
> running SQL Server you are probably OK just disabling them with the
> affinity mask. But if you really want to see for sure if HT is helping or
> hurting I really suggest you turn it off at the BIOS level so there is no
> mistaking the effects. Personally I wish they never invented HT
> processors and everything was dual core. While I have seen some cases
> where HT made a positive difference I have seen far too many where its
> behavior was misunderstood and it ended up hurting more than helping. I
> wouldn't recommend anyone buy HT enable processors for SQL Server going
> forward.
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:OOrYfVqgHHA.3412@.TK2MSFTNGP02.phx.gbl...
>|||Chris,
I have to say I have never seen a system with 3 processors. CXPackets can be
from several other things as well. Just poor parallel processing in general
can cause these waits. I don't remember too much of the original thread
but setting your MAXDOP to 1 can get rid of any simply due to parallelism
but will not fully alleviate the issues of HT. Are you planning on
upgrading to2005 or even SP4 any time soon? What OS are you using? If it is
Win2000 then you are even further behind when it come to HT. Just buy a new
server, its only money
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23l28mErgHHA.4156@.TK2MSFTNGP02.phx.gbl...
> Andrew,
> Some weeks ago we moved a highly I/O intense SQL application off of an SQL
> clustered server to its own SQL cluster because it could not live with
> another SQL application. After some significant tuning of the code, both
> apps, and dropping of old data we are about to put them back together
> again. The clustered SQL server has 3 hyperthreaded CPU's with
> hyperthreading switched on and available to SQL2000 SP3A build 818. I had
> read that hyperthreading was not the best for SQL2000 and wanted to see if
> notable SQL experts would comment. We did see some CXPACKET waits before
> and from what you are saying if we see them again disabling hyperthreading
> could be the best action rather than using the affinity mask.
> Thanks so much for your valued opinion.
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23AFJZsqgHHA.4140@.TK2MSFTNGP05.phx.gbl...
>|||Andrew,
We have held off from SP4 because of the move to a new server (the one the
I/O app went to) and SQL2005 SP2. One app is starting converting to SQL2005
and the other will follow when we have enough staff. Both servers are W2K3
with SP1 but the old server has less than 4Gb memory while the new one has
6Gb memory with 4Gb for SQL. Once they are back together again we will be
shoehorning 2 apps into less SQL cache so while I expect faster response
than what is was before the separation I do not expect it to be anywhere as
fast as when they were on their own.
We may have to water down HT to make things work better.
Thanks again.
Hope to see you in Denver at PASS. I am sure I heard you talk in Orlando in
2004 PASS.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u7DtGnsgHHA.4260@.TK2MSFTNGP03.phx.gbl...
> Chris,
> I have to say I have never seen a system with 3 processors. CXPackets can
> be from several other things as well. Just poor parallel processing in
> general can cause these waits. I don't remember too much of the original
> thread but setting your MAXDOP to 1 can get rid of any simply due to
> parallelism but will not fully alleviate the issues of HT. Are you
> planning on upgrading to2005 or even SP4 any time soon? What OS are you
> using? If it is Win2000 then you are even further behind when it come to
> HT. Just buy a new server, its only money
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23l28mErgHHA.4156@.TK2MSFTNGP02.phx.gbl...
>|||Chris,
I will be speaking at PASS in Denver this year along with TechEd in Orlando
and probably Dev Connections in Vegas as well. So if you are at any one of
them please look me up.
See you then.
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23I1RD9sgHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> We have held off from SP4 because of the move to a new server (the one the
> I/O app went to) and SQL2005 SP2. One app is starting converting to
> SQL2005 and the other will follow when we have enough staff. Both servers
> are W2K3 with SP1 but the old server has less than 4Gb memory while the
> new one has 6Gb memory with 4Gb for SQL. Once they are back together again
> we will be shoehorning 2 apps into less SQL cache so while I expect faster
> response than what is was before the separation I do not expect it to be
> anywhere as fast as when they were on their own.
> We may have to water down HT to make things work better.
> Thanks again.
> Hope to see you in Denver at PASS. I am sure I heard you talk in Orlando
> in 2004 PASS.
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u7DtGnsgHHA.4260@.TK2MSFTNGP03.phx.gbl...
>