We just switched to a machine using 2 Xeon 2.2GHz computers and 2GM RAM. Some of the querys that had run fine on the older 2 PentiumIII 512MB computer now fail with the message: Intra-query parallelism caused your server command (process ID #50) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1). I added the query hint MAXDOP 1 to the select statement that is causing the problem but it did not help. We installed SP 3a before copying the data to the new server. The only way I can keep the query from failing is to actuall set the max degree of parallelism to 1:
sp_configure 'max degree of parallelism', 1
go
reconfigure with override
But doesn't that defeat the purpose of multiple processors. The machine thiks we have 4 processors because of the Hyper Threading (Enterprise Manager shows 4 processors). Could that have something to do with the problem?

David A. Beck

David,

We use dual Xeon processors as well. Intra-query parallelism problems for us were mostly fixed by SP3. We only seemed to have the problem on some heavily self-joining operations that worked through several views.

Simplifying the query also may help, if you have anything nearly as complex.

Setting Max Degree of Parallelism to 1 will not totally defeat the 4 processors, it will just keep it from using more than 1 processor for a single query. This will affect a few other things, such as some DBCC functinos, as this link from the BOL describes.

max degree of parallelism Option
http://tinyurl.com/yt433

Russell Fields
"David A. Beck" <davidb> wrote in message news:3416
We just switched to a machine using 2 Xeon 2.2GHz computers and 2GM RAM. Some of the querys that had run fine on the older 2 PentiumIII 512MB computer now fail with the message: Intra-query parallelism caused your server command (process ID #50) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1). I added the query hint MAXDOP 1 to the select statement that is causing the problem but it did not help. We installed SP 3a before copying the data to the new server. The only way I can keep the query from failing is to actuall set the max degree of parallelism to 1:
sp_configure 'max degree of parallelism', 1
go
reconfigure with override
But doesn't that defeat the purpose of multiple processors. The machine thiks we have 4 processors because of the Hyper Threading (Enterprise Manager shows 4 processors). Could that have something to do with the problem?

David A. Beck

if you did have this problem on a PIII 2P system, but only
on the Xeon 2P system,
then setting
sp_configure 'max degree of parallelism', 2
should also work.
sometimes intra-query parallelism can cause a deadlock
when used on multiple physical processors without HT (and
MS has eliminated many of these)
but in your case, the deadlock was probably caused by
trying to use parallelism on both logical processors of
each phyical processor.

it depends on why you bought multiple processors.
in OLTP apps, it probably is to increase throughput
in DW apps, it might be to make individual queries run
faster.
using parallelism may actually lower throughput in OLTP
applications

>-----Original Message-----
>We just switched to a machine using 2 Xeon 2.2GHz
computers and 2GM RAM. Some of the querys that had run
fine on the older 2 PentiumIII 512MB computer now fail
with the message: Intra-query parallelism caused your
server command (process ID #50) to deadlock. Rerun the
query without intra-query parallelism by using the query
hint option (maxdop 1). I added the query hint MAXDOP 1 to
the select statement that is causing the problem but it
did not help. We installed SP 3a before copying the data
to the new server. The only way I can keep the query from
failing is to actuall set the max degree of parallelism to
1:
>sp_configure 'max degree of parallelism', 1
>go
>reconfigure with override
>But doesn't that defeat the purpose of multiple
processors. The machine thiks we have 4 processors because
of the Hyper Threading (Enterprise Manager shows 4
processors). Could that have something to do with the
problem?

Thanks Joe. I did try setting the 'max degree of parallelism' to 2 with the
same results. I don't think OLTP apps are our problem here. I think Russell
may be into something. We try to set up the database for a mimimum of
repeating fields and duplicated data but rely on joins to tie all the
relational data togehter. This particular one has a couple of joins on it. I
was surpreised when it ignored the query hint.

David A. Beck
"joe chang" <anonymous> wrote in message
news:280a
[..]

Hi David,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

From the information you provide, that is when executing an complex query
on multi-processor computer, you met the error message as follows:
'Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused
your server command (process ID #50) to deadlock. Rerun the query without
intra-query parallelism by usingthe query hint option (maxdop 1). ', right?

If the error message is not exactly the same as the above, please provide
more detailed information for us to analysis. If it is exactly the same, I
have to say that it is an known issue of SQL Server 2000. To avoid this
error message, in Query Analyzer, run the following code to turn off max
degree of parallelism:
sp_configure 'max degree of parallelism', 1
go
reconfigure with override
go

I will keep on monitor this issue and I will inform you any update of
solving it as soon as possible. If you still have questions about it,
please feel free to post new message here and I am ready to help! Thanks.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Thanks, this is what I did and the problem did go away. I think Russell
Fields was the first on the string to suggest it. Ressell told me that
this would not hurt for the most part after I suggested that I was
defeating the purpose of the multiple processors. This seems only to
happen, to us, on machines with multiple processors using HT.

David A. Beck

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Baisong,

We are running on the new server now and have just started doing our
large end-of-month runs. Some runs are quite a bit slower on SQL2K even
thought the processers are larger, the disks are faster, and we have 4
times the memory (2GB). One run in particular does joins on a large
table to give us a net worth analysys. On the SQL7 machine, this takes 5
hours. It's still running on the larger SQL2K machine that we set the
parameter so that it only uses one processor for a query. I estimate it
will take 36 hours. The database is EXACTLY the same structure as it was
on the SQL7 machine. Is Microsoft any closer to solving this problem so
I can allow the machine to use all of it's processors? It would be a BIG
hastle to go back to SQL7.

David A. Beck


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi David,

Thank you for using the newsgroup.

I check the information of this problem again but unfortunately, since it
is a really complicated one, our engineers are still working on it. I will
monitor the progress and will inform you as soon as possible if it is
fixed. On the present situation, I have to say that you have to use the
workaround in my last reply.

Thanks for you understanding.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Baisong,

Thanks for keeping checking. One thing I noticed is that on the old
machine we did not have the BOOST performance checked and on the new one
we do. I look forward to the hotfix you guys are working on.

David A. Beck


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!