Effects of Intel Hyperthreading on SQL Server

Can anyone share their knowledge of the subject?
Thank you,
BryanNo deleterious effects. Just makes one processor appear as two
(per-processor licensing is based on physical processors, not virtual).
"BDB" <reply@.to.group.com> wrote in message
news:O%23iTVCvWFHA.3240@.TK2MSFTNGP10.phx.gbl...
> Can anyone share their knowledge of the subject?
> Thank you,
> Bryan
>|||Works pretty good most of the time. There are a few hotfixes that address
some obscure issues on highly scaled machines (8+ physical processors) but
those only apply to a very few systems. OLTP environments work very well on
HT boxes. I do limit the maximum dogree of parallelism to the actual
physical processor count to avoid over-saturating the box on a single query.
Geoff N. Hiten
Microsoft SQL Server MVP
"BDB" <reply@.to.group.com> wrote in message
news:O%23iTVCvWFHA.3240@.TK2MSFTNGP10.phx.gbl...
> Can anyone share their knowledge of the subject?
> Thank you,
> Bryan
>|||Consider limiting the number of processors user per query to max same number as your physical
processors. Also, if you get bad query plans, investigate the MAXDOP query hint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BDB" <reply@.to.group.com> wrote in message news:O%23iTVCvWFHA.3240@.TK2MSFTNGP10.phx.gbl...
> Can anyone share their knowledge of the subject?
> Thank you,
> Bryan
>|||BDB wrote:
> Can anyone share their knowledge of the subject?
> Thank you,
> Bryan
SQL Server 2000 SP3 was made "aware" of hyperthreading and will
distinguish between a logical and physical processor when deciding on
parallel plans.
--
David Gugick
Imceda Software
www.imceda.com|||In article <O#iTVCvWFHA.3240@.TK2MSFTNGP10.phx.gbl>, reply@.to.group.com
says...
> Can anyone share their knowledge of the subject?
We have single, dual and Quad Xeon CPU servers with MS SQL 2000 on them.
When HT is enabled we see 0% to 28% increase in performance for SOME
tasks, but not all.
The real performance increase is in reindexing the tables on a weekly or
monthly basis, at least for our applications were more than 2mm records
are imported every night m-f.
What I see with HT is that you "can" performance tune your SQL server
for it, but on a Single CPU Xeon server with HT enabled there seems to
be little difference, the most difference was on the Duals and the least
difference was on the Quads.
--
spam999free@.rrohio.com
remove 999 in order to email me|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u$G9YfvWFHA.3572@.TK2MSFTNGP12.phx.gbl...
> BDB wrote:
>> Can anyone share their knowledge of the subject?
>> Thank you,
>> Bryan
> SQL Server 2000 SP3 was made "aware" of hyperthreading and will
> distinguish between a logical and physical processor when deciding on
> parallel plans.
> --
> David Gugick
> Imceda Software
> www.imceda.com
David,
Thanks for your reply.
I've looked for an article on this topic for SP3. Can you specify a link or
KB #?
Thank you,
Bryan|||BDB wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:u$G9YfvWFHA.3572@.TK2MSFTNGP12.phx.gbl...
>> BDB wrote:
>> Can anyone share their knowledge of the subject?
>> Thank you,
>> Bryan
>> SQL Server 2000 SP3 was made "aware" of hyperthreading and will
>> distinguish between a logical and physical processor when deciding on
>> parallel plans.
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
> David,
> Thanks for your reply.
> I've looked for an article on this topic for SP3. Can you specify a
> link or KB #?
> Thank you,
> Bryan
I'm afraid the information came from the SQL Server group. I don't know
if it was every documented, although I suspect it's somewhere on the MS
site. But here's a start:
http://www.microsoft.com/sql/howtobuy/SQLonHTT.doc
http://support.microsoft.com/kb/835864
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OXp3rIzWFHA.3620@.TK2MSFTNGP09.phx.gbl...
> BDB wrote:
>> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
>> news:u$G9YfvWFHA.3572@.TK2MSFTNGP12.phx.gbl...
>> BDB wrote:
>> Can anyone share their knowledge of the subject?
>> Thank you,
>> Bryan
>> SQL Server 2000 SP3 was made "aware" of hyperthreading and will
>> distinguish between a logical and physical processor when deciding on
>> parallel plans.
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>> David,
>> Thanks for your reply.
>> I've looked for an article on this topic for SP3. Can you specify a
>> link or KB #?
>> Thank you,
>> Bryan
> I'm afraid the information came from the SQL Server group. I don't know if
> it was every documented, although I suspect it's somewhere on the MS site.
> But here's a start:
> http://www.microsoft.com/sql/howtobuy/SQLonHTT.doc
> http://support.microsoft.com/kb/835864
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
Thanks again.|||I did some testing on HT processors using dual and quad cpu boxes (i.e. physical chips) with 500 and 1000 OLTP user loads. The HT processors added around 30% and in some cases even more performance. However, this was a strictly OLTP application with very targeted SELECT statements and oodles of single record INSERT, UPDATE, and DELETE statements. All in all, the HT processors provided a nice boost.
-Kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> Can anyone share their knowledge of the subject?
> Thank you,
> Bryan|||While SP3 added some slight intelligence towards HT you really need Win2003
to take full advantage of it.
--
Andrew J. Kelly SQL MVP
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u$G9YfvWFHA.3572@.TK2MSFTNGP12.phx.gbl...
> BDB wrote:
>> Can anyone share their knowledge of the subject?
>> Thank you,
>> Bryan
> SQL Server 2000 SP3 was made "aware" of hyperthreading and will
> distinguish between a logical and physical processor when deciding on
> parallel plans.
> --
> David Gugick
> Imceda Software
> www.imceda.com