deadlocks involving parallelism

[T Dubya]

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 -

http://support.microsoft.com/?kbid=837983

The only suggestion that actually yielded results was turning off
parallel query processing but I don't want to give up what should be a
performance advantage if it wasn't for the deadlocks. Query tuning and
index tuning hasn't helped. Any suggestions? I haven't applied SP4
yet. I'm wondering if anyone has seen the same problem resolved with
SP4.

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

T Dubya (ti*********@bi gfoot.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, es****@sommarsk og.se

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

[T Dubya]

Thanks for the suggestion. I'll give it a try.
I found a "Best Practices" note in my Microsoft SQL Server 2000
Administrators Pocket Consultant on page 38 that recommends not
assigning the higher numbered processors (5,6,7, and 8) to the SQL
Server. It goes on to explain that Windows assigns deferred process
calls associated with network interface cards to the highest numbered
processors. If the system has two NICs, for example, the calls would be
directed to CPUs 7 and 8. Even though the default installation made
processors 0 through 7 available to the SQL Server it sounds like the
recommendation is to only make 0 through 3 available. What do you
think? Perhaps this would have the same effect as only assigning 4
processors for parallel execution of queries.

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

T Dubya (ti*********@bi gfoot.com) writes:

Thanks for the suggestion. I'll give it a try.
I found a "Best Practices" note in my Microsoft SQL Server 2000
Administrators Pocket Consultant on page 38 that recommends not
assigning the higher numbered processors (5,6,7, and 8) to the SQL
Server. It goes on to explain that Windows assigns deferred process
calls associated with network interface cards to the highest numbered
processors. If the system has two NICs, for example, the calls would be
directed to CPUs 7 and 8. Even though the default installation made
processors 0 through 7 available to the SQL Server it sounds like the
recommendation is to only make 0 through 3 available. What do you
think? Perhaps this would have the same effect as only assigning 4
processors for parallel execution of queries.

I will have to admit that the discussion went over my head here. If CPU:s
0-3 are the "default CPU" of each physical processor, this seems like
a good choice. I will have to admit that I don't know how processors
are numbered in a multi-processor HT box.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

[Bjarke Wedemeijer]

Erland, Dubaya,

your problem is SQL Server 2000 SP3 - SP3 is not hiperthread aware
which means that if your query is parallelized into several worker
threads, these threads might end-up running concurrently on the
same physical processor, which means 2 threads running on 1 physical
processor due to Hyperthreading. I know that there have been made some
changes in build 818, and SP4, especially regarding HT and NUMA -
what you basically sohuld do is test your situation with build 818 or
SP4,
or turn off hyperthreading. Test, but be aware that Hyperthreading
is only giving you maybe 10% extra performance if you're lucky,
whereas
your parallisme within SQL Server can give you enormous amounts of
performance gains. Its no secret that Intel made hyperthreading since
the extra thread could run Antivirus software while the CPU was more
a less idle in some of their components. Running SQL Server 2000 with
hyperthreading can give you some headaches, try running on the latest
build
or turn of hyperthreading.