The only time you turn it off, as far as I know of, is when you have the max
number of processors installed already.

Example: Windows XP support only 2 processors, if you have two already, turn
off Hyperthreading.
Example: Windows Enterprise supports 8 processors, if you have 8 already,
turn off Hyperthreading.

So, how many processors are in your nodes?

Cheers,

Rod

"DaveK" <anonymous (AT) discussions (DOT) microsoft.com> wrote

problem. However, one thing was curious. I didn't speak to Moft but the
person who did said MS said NOT to turn hyperthreading on SQL clusters on!
Now that shocked me. Does anyone know why? We turned it off to see whether
it was reponsible for some performance issues and results were inconclusive.
So we were going to turn it back on. We were strongly advised not to do so.
So does anyone know why or could point me in the right direction? I've never
heard this mentioned before.

Not exactly.

I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16 virtual
processors just fine. I believe that Service Pack 2 contained the code to
fix the virtual/physical processor count issue. Build 816 and 910 (post SP3
hotfixes) contain some code to improve performance on Hyperthreaded systems.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"Rodney R. Fournier [MVP]" <rod (AT) die (DOT) spam.die.nw-america.com> wrote in
message news:%23PHgs2%23REHA.3344 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

HP recommends to turn it off on XP if you have two processors. I don't have
time to lookup the article right now. With it on or off on XP, I did not see
a difference.

Cheers,

Rod

"Geoff N. Hiten" <SRDBA (AT) Careerbuilder (DOT) com> wrote

I agree with the XP recommendation. For a dedicated SQL server, I see a
significant difference between off and on. (Unisys ES7000 8x2.8GHz) Average
CPU 26% on, 40% off.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"Rodney R. Fournier [MVP]" <rod (AT) die (DOT) spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

Sweet, I know I turn it on with only 2 procs, have not run into an 8 way
with it on or off. Great info, thanks!

Cheers,

Rod

"Geoff N. Hiten" <SRDBA (AT) Careerbuilder (DOT) com> wrote

Hopefully your not trying to run a cluster on WinXP anyway<g>.

--

Andrew J. Kelly
SQL Server MVP


"Rodney R. Fournier [MVP]" <rod (AT) die (DOT) spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

Dave,

We put 910 into place less than a week ago when we found out why we had to have it: previously - 818 - SQL 2000 allows all logins access to the system before tempdb comes online. Well, we are a web-based company. We are running an active/active SQL cluster on a Win2K3 server ent ed cluster with HT enabled. So, I'm concerned to hear your comment about this option. Now, yesterday we see odd behavior on the system. Specifically the following error:

Transaction (Process ID 350) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

Sure, we've had deadlocks before but, not this type "communication buffer".

Another thing we've noticed is that if a process wants to go parallel, we've seen it go to 49 degrees of parallelism. You can imagine all of our CPUs are then spiking (2 servers x 8 CPUs each x HT), which in turn doesn't make the rest of the processes happy.

We only have SQL Server running on these servers. All web servers (over 40) can hit this cluster at any given time.

Would love to have a discussion with you. Microsoft told us that we are only the second company that reported needing the 910 patch. So, I'm expecting you are the other company. Are you web-based? Or, have lots of web server activity against your database servers?

We've seen some other odd behavior as well. But, this e-mail is getting lengthy.

I see you are in the UK. We are in the USA.

Your reply is appreciated,

Doug

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

910 was built specifically for us after we reported a bug via Unisys.
Technically the build was for Unisys under their Premier agreement, but we
were the end customer.

I have also seen extra-high parallelism. I have limited the degree of
parallelism intentionally since I prefer higher response to the many
transactional queries rather than the few analysis-type queries.

Yes, we are very web-based. About 85-90% of our pages are .Net based. We
are the number one job board in the US (we recently passed Monster).


--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"Doug Thomas" <dougt (AT) ecollege (DOT) com> wrote

before tempdb comes online. Well, we are a web-based company. We are running
an active/active SQL cluster on a Win2K3 server ent ed cluster with HT
enabled. So, I'm concerned to hear your comment about this option. Now,
yesterday we see odd behavior on the system. Specifically the following
error:
victim. Rerun the transaction..
CPUs are then spiking (2 servers x 8 CPUs each x HT), which in turn doesn't
make the rest of the processes happy.
expecting you are the other company. Are you web-based? Or, have lots of web
server activity against your database servers?