A former colleague recently notified me about some some 'benchmark'
testing he'd conducted with SQL Server and Hyperthreading - with some
interesting results. Basically the conclusion he reached was "HT gives
performance increases some of the time but most of the time it really
screws performance and you are better off disabling it.". The results
of his test queries were as follows:

HT turned on, SQL Server using all 4 logical CPUs = 2 mins 41 secs
HT turned off, SQL Server using all 2 CPUs = 21 secs

This pretty much goes against everything i've read about the perfomance
benefits of Hyperthreading, and I was wondering, before I start testing
on our own servers - Has anyone else had similar results (or opposite
for that matter)?

I've encountered issues with parallelism slowing queries down significantly.
If you can, ask your friend to try the same test, with HT turned on, but add
OPTION (MAXDOP 1) to the end of the queries.  He should be able to compare
the query plans to determine if parallelism is at fault.

<lovemyalpa***@gmail.com> wrote in message
Show quoteHide quote

On 12 May 2005 07:08:55 -0700, lovemyalpa***@gmail.com wrote:
Show quoteHide quoteWell, first of all, if you run benchmarks on SQLServer and publish the
results, Bill Gates will come to your house in the middle of the night
and, um, personally serve you papers for an injunction for violating
your EULA.

Second, Intel wants to sell you hyperthreading because it's new and
kewl, and you can't believe everything you read.

The problem is that running two logical threads may overload the
processor cache.  So for some simple but CPU bound jobs hyperthreading
might be a big win, but for complex jobs that are already memory hogs,
hyperthreading might lose big - you only have to have a few percent
more cache misses to screw up both the threads by big factors.

So what's a boy to do?

Well, I'm going to guess that for SQLServer apps, hyperthreading is
going to hurt at least as often as it helps.   Until and unless Intel
is putting humongous cache on these newer chips. 

Your mileage may vary.

Josh

Whether you get a performance boost from HT or not depends mainly on what
you are doing.  The boosts come when two threads use different parts of the
processor at the same time.  If the two threads try to do the same type of
action (I/O, calculations etc) then you will most likely get a decrease.
You should almost never set the MAXDOP to greater than the # of physical
procs in the machine.  In some cases you may even want to restrict
processing to only the physical processors.  But it really depends on the
workload.  You can not run one set or series of tests and say this is how it
is.  Everyone needs to test under their specific hardware config and
application usage.

Show quoteHide quote

Hi

Yesterday at SQL PASS conference in Munich, Gert Drapers (MS Architect)
explained the issues.

The problem with a HT CPU that the 2 simultaneous threads running on the CPU
both contend for the *same* L1, L2 and L3 processor cache. During heavy
processing, you may actually have the 2 clearing each other's cache. This
results in each having to go to main system RAM to work.

So, if you have 2 physical HT processors (looks like 4 to SQL Server and the
OS), set SQL Server to use only 2 processors for parallel query execution.
Server Properties Screen, change the Parallelism setting.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

<lovemyalpa***@gmail.com> wrote in message
Show quoteHide quote

The server encountered a stack overflow during compile time.
SP4 reports queries blocking on themselves?
Return a horizontal table for result
comparing text fields - Second try
what port my sql server is running on ??? 1433/UDB !!???