Hyper-Threading with SQL 2000 Standard

We just purchased a quad processor server running windows
2003. The processors are xeon so they have HTT
capability. Will SQL200 recognize all eight logical
processor or will it only see four. I wan't to find out
before we install SQL 2000. Thanks.With sp3 it should use all eight.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:297901c3af9b$78aa3c40$a601280a@.phx.gbl...
> We just purchased a quad processor server running windows
> 2003. The processors are xeon so they have HTT
> capability. Will SQL200 recognize all eight logical
> processor or will it only see four. I wan't to find out
> before we install SQL 2000. Thanks.|||Here's a good white-paper on hyper-threading for various Windows OS'
http://www.microsoft.com/windows2000/server/evaluation/performance/reports/hyperthread.asp
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<OS1YGh6rDHA.2456@.TK2MSFTNGP12.phx.gbl>...
> With sp3 it should use all eight.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:297901c3af9b$78aa3c40$a601280a@.phx.gbl...
> > We just purchased a quad processor server running windows
> > 2003. The processors are xeon so they have HTT
> > capability. Will SQL200 recognize all eight logical
> > processor or will it only see four. I wan't to find out
> > before we install SQL 2000. Thanks.

Hyperthreading SQL Server 7 - HELP!

Could you clarify the following.
We are looking to buy a quad xeon box (ie 8 virtual processors). From
reading MS documentation it looks like W2K Server won't cut it - we
will need to move to W2K Advanced Server, correct?
Secondly, will Sql Server 7 support 8 virtual processors under such a
scenario?
Thirdly, based on our proposed box 4 x 2.0 GHz Xeon + 2 Gb RAM - for
more performance would you increase the processor speed (eg to 2.5GHz)
or add more RAM (eg to 4 Gb, or more)?
Many Thanks,
StewartI think win2k server ONLY supports 2 physical procs, so you'll neet Advanced
Server...I think you'll also need sql 7 Enterprise to support 4 procs...
Please make sure SQL 7 Enterprise will run on W2k Advanced Server... ( I
think it will, but be safe and double check..)
"Stewart" <windsurfin_stew@.yahoo.com.au> wrote in message
news:9373ee00.0308112138.1cb2f916@.posting.google.com...
> Could you clarify the following.
> We are looking to buy a quad xeon box (ie 8 virtual processors). From
> reading MS documentation it looks like W2K Server won't cut it - we
> will need to move to W2K Advanced Server, correct?
> Secondly, will Sql Server 7 support 8 virtual processors under such a
> scenario?
> Thirdly, based on our proposed box 4 x 2.0 GHz Xeon + 2 Gb RAM - for
> more performance would you increase the processor speed (eg to 2.5GHz)
> or add more RAM (eg to 4 Gb, or more)?
> Many Thanks,
> Stewart|||Win 2K server supports 4 Procs.
Here is a good doc on Microsoft , Win2K and hyperthreading
http://www.microsoft.com/windows2000/docs/hyperthreading.doc
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:ue9wXLLYDHA.1640@.TK2MSFTNGP10.phx.gbl...
> I think win2k server ONLY supports 2 physical procs, so you'll neet
Advanced
> Server...I think you'll also need sql 7 Enterprise to support 4 procs...
> Please make sure SQL 7 Enterprise will run on W2k Advanced Server... ( I
> think it will, but be safe and double check..)
> "Stewart" <windsurfin_stew@.yahoo.com.au> wrote in message
> news:9373ee00.0308112138.1cb2f916@.posting.google.com...
> > Could you clarify the following.
> >
> > We are looking to buy a quad xeon box (ie 8 virtual processors). From
> > reading MS documentation it looks like W2K Server won't cut it - we
> > will need to move to W2K Advanced Server, correct?
> >
> > Secondly, will Sql Server 7 support 8 virtual processors under such a
> > scenario?
> >
> > Thirdly, based on our proposed box 4 x 2.0 GHz Xeon + 2 Gb RAM - for
> > more performance would you increase the processor speed (eg to 2.5GHz)
> > or add more RAM (eg to 4 Gb, or more)?
> >
> > Many Thanks,
> >
> > Stewart
>|||As for your performance question. You will definitely buy more power by
adding memory instead of CPU here. Go with 4GB RAM.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:e3GbgjLYDHA.1280@.tk2msftngp13.phx.gbl...
> Win 2K server supports 4 Procs.
> Here is a good doc on Microsoft , Win2K and hyperthreading
> http://www.microsoft.com/windows2000/docs/hyperthreading.doc
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:ue9wXLLYDHA.1640@.TK2MSFTNGP10.phx.gbl...
> > I think win2k server ONLY supports 2 physical procs, so you'll neet
> Advanced
> > Server...I think you'll also need sql 7 Enterprise to support 4
procs...
> > Please make sure SQL 7 Enterprise will run on W2k Advanced Server... ( I
> > think it will, but be safe and double check..)
> > "Stewart" <windsurfin_stew@.yahoo.com.au> wrote in message
> > news:9373ee00.0308112138.1cb2f916@.posting.google.com...
> > > Could you clarify the following.
> > >
> > > We are looking to buy a quad xeon box (ie 8 virtual processors). From
> > > reading MS documentation it looks like W2K Server won't cut it - we
> > > will need to move to W2K Advanced Server, correct?
> > >
> > > Secondly, will Sql Server 7 support 8 virtual processors under such a
> > > scenario?
> > >
> > > Thirdly, based on our proposed box 4 x 2.0 GHz Xeon + 2 Gb RAM - for
> > > more performance would you increase the processor speed (eg to 2.5GHz)
> > > or add more RAM (eg to 4 Gb, or more)?
> > >
> > > Many Thanks,
> > >
> > > Stewart
> >
> >
>|||SQL Server 7.0 Standard Edition will only support 4 processors, and it can't
tell the difference between physical and logical processors. So, if you
want it to use all 8 logical processors on your proposed configuration
you'll want to purchase Enterprise Edition. In addition, 2GB of memory is
marginal for this sized configuration if you are really worried about
maximum performance. So you'll want more memory and Enterprise Edition to
support that additional memory. With 7.0 the best you can do for memory is
the /3GB switch. So the maximum useful memory on the system is going to be
4GB.
An alternative is to stick with Standard Edition and just have it use one
logical processor on each physical processor. Background tasks (Windows
processes, SQL Agent, etc.) will make some use of the additional logical
processors. Keep in mind that the additional logical processors don't
double system performance anyway, most of the data I've heard suggests
they'll give you about a 20% boost. And if you make heavy use of parallel
query then they might actually lead to some pathological conditions that
degrade performance. Finally, with Standard Edition you'd still be limited
to 2GB of memory, which in the long run is your most likely bottleneck.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Stewart" <windsurfin_stew@.yahoo.com.au> wrote in message
news:9373ee00.0308112138.1cb2f916@.posting.google.com...
> Could you clarify the following.
> We are looking to buy a quad xeon box (ie 8 virtual processors). From
> reading MS documentation it looks like W2K Server won't cut it - we
> will need to move to W2K Advanced Server, correct?
> Secondly, will Sql Server 7 support 8 virtual processors under such a
> scenario?
> Thirdly, based on our proposed box 4 x 2.0 GHz Xeon + 2 Gb RAM - for
> more performance would you increase the processor speed (eg to 2.5GHz)
> or add more RAM (eg to 4 Gb, or more)?
> Many Thanks,
> Stewart

Hyperthreading

Hi,
We are using a quad Xeon with hyperthreading. Windows 2000 and SQL Server "see" 8 processors. We have scheduled SQL to only use CPUs 0,1,2,3. The server is a dedicated database server. Has anyone done much work with hyperthreading? Is our approach sensible?
Ken"Ken Ng" <anonymous@.discussions.microsoft.com> wrote in message
news:72043C98-B317-4CA2-A97D-36AC17A5ABA0@.microsoft.com...
> Hi,
> We are using a quad Xeon with hyperthreading. Windows 2000 and SQL Server
"see" 8 processors. We have scheduled SQL to only use CPUs 0,1,2,3. The
server is a dedicated database server. Has anyone done much work with
hyperthreading? Is our approach sensible?
>
This will essentially disable hyperthreading for SQL server since CPUs
0,1,2,3 are the first logical processesors on each chip. So the system will
run basically like a non-hyperthreading 4-way server.
But you should get better performance if you let SQL Server schedule threads
on all 8 logical processors. The point of HT is that scheduling 2 threads
on each physical processor allows a higher utilization of internal CPU
resources, and results in a higher throughput than switching between the 2
threads.
David|||i have not found anything in SQL Server that benefits from
HT in a useful manner.
however, HT and parallel execution plans may in certain
circumstances have potentially crippling effect.
i suggest that HT be disabled unless you have conducted a
reasonably comphrensive test on whether there are any ill
effects
>--Original Message--
>Hi,
>We are using a quad Xeon with hyperthreading. Windows
2000 and SQL Server "see" 8 processors. We have scheduled
SQL to only use CPUs 0,1,2,3. The server is a dedicated
database server. Has anyone done much work with
hyperthreading? Is our approach sensible?
>Ken
>.
>|||In my tests performance with HT switched ON was about 15%
WORSE than with HT switched OFF. Physical processor is a
physical processor and when SQL Server creates parallel
execution plan and is trying to run simultaneously two
subqueries on the same physical processor, it creates a
bottleneck.
Cheers,
Alex|||I have no evidence for this, but if parallellism is not used (for
example just OLTP), then I expect a HT system to perform 15% better than
the same system with HT disabled. If parallellism is used (sometimes),
then I guess it is better to turn off HT.
Gert-Jan
joe chang wrote:
> i have not found anything in SQL Server that benefits from
> HT in a useful manner.
> however, HT and parallel execution plans may in certain
> circumstances have potentially crippling effect.
> i suggest that HT be disabled unless you have conducted a
> reasonably comphrensive test on whether there are any ill
> effects
> >--Original Message--
> >Hi,
> >
> >We are using a quad Xeon with hyperthreading. Windows
> 2000 and SQL Server "see" 8 processors. We have scheduled
> SQL to only use CPUs 0,1,2,3. The server is a dedicated
> database server. Has anyone done much work with
> hyperthreading? Is our approach sensible?
> >
> >Ken
> >.
> >|||> This will essentially disable hyperthreading for SQL server since CPUs
> 0,1,2,3 are the first logical processesors on each chip. So the system will
> run basically like a non-hyperthreading 4-way server.
However, the OS can still use the other 4 logical processors.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:e1d$qQPuDHA.2448@.TK2MSFTNGP09.phx.gbl...
> "Ken Ng" <anonymous@.discussions.microsoft.com> wrote in message
> news:72043C98-B317-4CA2-A97D-36AC17A5ABA0@.microsoft.com...
> > Hi,
> >
> > We are using a quad Xeon with hyperthreading. Windows 2000 and SQL Server
> "see" 8 processors. We have scheduled SQL to only use CPUs 0,1,2,3. The
> server is a dedicated database server. Has anyone done much work with
> hyperthreading? Is our approach sensible?
> >
> This will essentially disable hyperthreading for SQL server since CPUs
> 0,1,2,3 are the first logical processesors on each chip. So the system will
> run basically like a non-hyperthreading 4-way server.
> But you should get better performance if you let SQL Server schedule threads
> on all 8 logical processors. The point of HT is that scheduling 2 threads
> on each physical processor allows a higher utilization of internal CPU
> resources, and results in a higher throughput than switching between the 2
> threads.
> David
>|||Thanks for the replies folks. From the discussion I deduce that HT may not be that beneficial for SQL per se. In our case, by limiting SQL to CPU 0,1,2,3 we seem to be disabling HT for SQL but enabling the extra 4 (logical) processors i.e. CPU 4,5,6,7 to be used by the o/s. This seems to be a reasonable approach
Many Thank
Ken Ng