Parallelelism pour performance

Question

Parallelelism pour performance

Roust_m

SSCoach

Points: 17410
More actions
November 22, 2004 at 6:22 am

#361623

Hi!
I have a similar query, that runs fo 30-60 seconds with [max degree of parallelism] equal 1 and for 5-20 minutes with [max degree of parallelism] equal 4 or 8 (I have a four-processor server with HT enabled):
---------------------------------------------------------------------------------------------
select sl.stock_lot, itsl.qty from dbo.STOCK_LOT sl (nolock)
            join dbo.INV_TRANS_STOCK_LOT itsl (nolock)   on itsl.stock_lot = sl.stock_lot
            join dbo.INV_TRANS_DETAIL itd (nolock) on itd.inv_trans_detail = itsl.inv_trans_detail
            left join dbo.INV_TRANS_STOCK_LOT itsl1 (nolock) on itsl1.stock_lot = sl.stock_lot and
itsl1.occur < itsl.occur
where itd.from_location < 0 and itd.to_location > 0 and itsl1.stock_lot is null
---------------------------------------------------------------------------------------------
The number of records is ~200 000 for the first table and ~2 000 000 for the others. The plans are similar, both use clustered index scan a number of times and the scans consume ~75% of the query time. What can be wrong? Are there any known parallelelism bugs?
Thanks.
November 25, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

We have seen turning the parallel on to certain degree has created heavy I/O into disk subsystem which becomes bottleneck for the performance.

You need try to find out what system resouces are being waitted for yoyr query.

http://support.microsoft.com/default.aspx?scid=kb;en-us;822101

http://support.microsoft.com/default.aspx?scid=kb;en-us;324692

It could be bug too.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 2

I have seen this effect also but have not had the opportunity to investigate.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

In addition, using more processors means managing more threads and requires more cache synchronization and SQL Server has more coordination to perform since it needs to slice and regroup the work spread over the processors. There are no golden rules, so it is wise to test it thoroughly before making a decision.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

We ran into a similar problem at work...

You have an IS NULL in the WHERE clause and while that is not in itself a problem, you've probably run into a documented bug with SQL Server. Basically, if parallelism is spawned and there is an IS NULL condition in the WHERE clause, the IS NULL is many times and unpredictably ignored. For UPDATEs, that can be a real problem because you could be updating a lot more than you really wanted.

The "work-around" is to set OPTION(MaxDop 1) as you've done but that's kind of a waste on a 4 processor system.

The fix is to install the latest security hot fix for SQL Server 2000 which just happens to contain the fix for the IS NULL problem. Unfortunately, that lastest hot fix breaks a couple of things concerning XML so you will also have to install that hot fix, as well.

For more information, see the following URL:

http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql2k

..AND, NO, IT DOESN'T AFFECT JUST COUNTS. IT AFFECTS EVERYTHING. (We found out the hard way!!! )

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)