Posted by Erland Sommarskog on 06/21/05 00:40

T Dubya (timber_toes@bigfoot.com) writes:
> We're experiencing a large number of deadlocks since we began running
> SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper
> threading intel processors. We don't have the same problem on Dell
> 6650's w/o the hyper threading. If I turn off the parallel query
> processing option the deadlocks stop. I've tried all of the suggestions
> from the Microsoft Knowledge Base under the following link -

A general recommendation is to change "max degree of parallelism" to
the number of physical processors. Whether this will help your parallelism
deadlocks, I don't know, but you should make that configuration anyway.

As it was explained to me, HT processors creates that extra CPU by
giving it idle cycles from the first processor. But if you have a
parallel query, those idle cycles are not really there, and you get
a serialization of the processing.

If that does not, try tracking down the query/ies that have this
problem, and add "OPTION (MAXDOP 1)" to these queries, to turn off
parallelism for these queries.

> I haven't applied SP4 yet. I'm wondering if anyone has seen the same
> problem resolved with SP4.

I have no idea if that will help, but some general notes on SP4:

SP4 is here: http://www.microsoft.com/sql/downloads/2000/sp4.mspx.
Please observe the note about AWE. The note is out of date, since
there actually is a fix for the AWE problem; just follow the link
in the note.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]