P4 xeon Hyperthreading

We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4 xeon 2.8
ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
processors.
I've heard that it performs better with HT turned off. Anyone here that?In general, no. However, SQL Server might parallize a query too much so setting maxdop to number pf
physical processors (using sp_configure) can be a good idea.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kevin Jackson" <softwiz@.covad.net> wrote in message news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4 xeon 2.8
> ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> processors.
> I've heard that it performs better with HT turned off. Anyone here that?
>|||If setting MaxDop to the actual no. of physical processor, wouldnt it not be
best to disable hyperthreading ?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uHaWj9EhDHA.1952@.TK2MSFTNGP12.phx.gbl...
> The only issue I have seen with Hyperthreading is oversaturation of CPU
> resources by too much parallelism. Set your MAX Degree of Parallelism
> (MADXOP) down to the actual physical processor count and HT works just
fine.
> --
> Geoff N. Hiten
> SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Kevin Jackson" <softwiz@.covad.net> wrote in message
> news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> >
> > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4 xeon
> 2.8
> > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> > processors.
> >
> > I've heard that it performs better with HT turned off. Anyone here
that?
> >
> >
>|||> If setting MaxDop to the actual no. of physical processor, wouldnt it
not be
> best to disable hyperthreading ?
MAXDOP applies to parallel queries only. The virtual processors can
benefit non-parallel queries as long as you don't disable
hyperthreading.
For example, on a dual Xeon with HT disabled, a CPU-bound parallel query
will degrade response time for other users. With HT enabled and MAXDOP
2, response time for the other users will be a bit better..
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"FR" <floydrev@.hotmail.com> wrote in message
news:u5y9r9LhDHA.3616@.TK2MSFTNGP11.phx.gbl...
> If setting MaxDop to the actual no. of physical processor, wouldnt it
not be
> best to disable hyperthreading ?
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uHaWj9EhDHA.1952@.TK2MSFTNGP12.phx.gbl...
> > The only issue I have seen with Hyperthreading is oversaturation of
CPU
> > resources by too much parallelism. Set your MAX Degree of
Parallelism
> > (MADXOP) down to the actual physical processor count and HT works
just
> fine.
> >
> > --
> > Geoff N. Hiten
> > SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> > "Kevin Jackson" <softwiz@.covad.net> wrote in message
> > news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> > >
> > > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad
P4 xeon
> > 2.8
> > > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees
8
> > > processors.
> > >
> > > I've heard that it performs better with HT turned off. Anyone
here
> that?
> > >
> > >
> >
> >
>|||> If you're not running advanced server and enterprise edition, you won't
> really be able to use more than 4 logical or physical processors.
Apparently SQL2K with sp3 should be HT aware and be able to use more logical processors then 4 on
SE.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message news:uLat%23WFhDHA.656@.TK2MSFTNGP12.phx.gbl...
> If you're not running advanced server and enterprise edition, you won't
> really be able to use more than 4 logical or physical processors.
> But to answer your question, it depends - we saw little difference, except
> that HT is off now everywhere because of OS/Hardware stability problems.
> Just test it for yourself.
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kevin Jackson" <softwiz@.covad.net> wrote in message
> news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> >
> > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4 xeon
> 2.8
> > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> > processors.
> >
> > I've heard that it performs better with HT turned off. Anyone here that?
> >
> >
>|||I don't think the OS will "mount" them ergo they're not available to SQL.
Not really sure tho.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ubaoQYYhDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > If you're not running advanced server and enterprise edition, you won't
> > really be able to use more than 4 logical or physical processors.
> Apparently SQL2K with sp3 should be HT aware and be able to use more
logical processors then 4 on
> SE.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:uLat%23WFhDHA.656@.TK2MSFTNGP12.phx.gbl...
> > If you're not running advanced server and enterprise edition, you won't
> > really be able to use more than 4 logical or physical processors.
> >
> > But to answer your question, it depends - we saw little difference,
except
> > that HT is off now everywhere because of OS/Hardware stability problems.
> >
> > Just test it for yourself.
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kevin Jackson" <softwiz@.covad.net> wrote in message
> > news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> > >
> > > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4
xeon
> > 2.8
> > > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> > > processors.
> > >
> > > I've heard that it performs better with HT turned off. Anyone here
that?
> > >
> > >
> >
> >
>|||From what I've heard the same goes fro the OS as for SQL Server. With some service pack, the OS
becomes HT aware and can use more logical processors then the edition allow.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message news:eHd$5O2hDHA.1048@.TK2MSFTNGP11.phx.gbl...
> I don't think the OS will "mount" them ergo they're not available to SQL.
> Not really sure tho.
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ubaoQYYhDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > > If you're not running advanced server and enterprise edition, you won't
> > > really be able to use more than 4 logical or physical processors.
> >
> > Apparently SQL2K with sp3 should be HT aware and be able to use more
> logical processors then 4 on
> > SE.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:uLat%23WFhDHA.656@.TK2MSFTNGP12.phx.gbl...
> > > If you're not running advanced server and enterprise edition, you won't
> > > really be able to use more than 4 logical or physical processors.
> > >
> > > But to answer your question, it depends - we saw little difference,
> except
> > > that HT is off now everywhere because of OS/Hardware stability problems.
> > >
> > > Just test it for yourself.
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > > "Kevin Jackson" <softwiz@.covad.net> wrote in message
> > > news:eBpRK29gDHA.1192@.TK2MSFTNGP12.phx.gbl...
> > > >
> > > > We are running SQL Server 2000 SP3 on Windows 2000 SP4 on a quad P4
> xeon
> > > 2.8
> > > > ghz with 12 gig of ram. Hyperthreading is turned on and SQL sees 8
> > > > processors.
> > > >
> > > > I've heard that it performs better with HT turned off. Anyone here
> that?
> > > >
> > > >
> > >
> > >
> >
> >
>