Important - CXPacket Errors in sysprocesses

Question

Important - CXPacket Errors in sysprocesses

SteveB-74825

Hall of Fame

Points: 3290
More actions
January 11, 2005 at 9:19 am

#165739

Hi All
I have a server with 6 processors and 8 gig of ram. I also have 2 tables, ah and sd
ah has 289 million rows and sd has 8 million
i need to extract the latest history from ah which involves running an aggregate and getting a max int along with the correcponding account_id
it takes 6 mins to extract the 7 million rows from ah into ms and another few seconds to index it.
now comes the tricky part.
my motivation for creating ms was to reduce the number of rows in ah that i was using to join to sd.
what i now need to do is join ah to ms and update sd with several values from ah.
whenever i do this i get CXPACKET waits on all threads in sysprocesses relating to my spid.
my question is this:
is this normal for the spec server i am using ?
has anyone esle come across this ?
is there any other way to join tables of this size ?
I believe the CXPACKET bug is described here http://support.microsoft.com/kb/293232/EN-US/

cheers
dbgeezer

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Well, does the kb article apply to you? Are you on the latest sp? Do you have parallellism set to ON? If so, tried turning it off?

/Kenneth

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 2

The article did apply to use - yes we are on the latest service pack - yes we had parallellism turned on and yes we tried turning it off. nothing helped.

i've rewritten the query and split it into the sections i described and the planned execution time is looking around 40 mins which is good - the last update is running now.

we think it's more of a memory problem on the server as it seem sql server simply cannot handle joins this large with the hardware we have.

i'll post the results here ...

cheers

dbgeezer

gbn SSCertifiable Points: 6429 More actions · Answer 3

CXPacket errors can occur when parallel queries require time for each sub-thread to catch up.

For example Dual CPU with Hyper-Threading can generate a 4 way parallel query. Because hyper-threading is not a full real CPU, then sometimes (and emphasise sometimes) the query will take minutes not the usual seconds depending on hwo SQL is spreading it's load across CPUs.

I've seen this and after monitoring and I decided against using "affinity mask" or "max degree of parallelism" to limit the server or the queries as it only happens in a small percentage of query runs.

What CPUS do you have - might this be it?

BTW, that KB applies to Standard Edition I trust you are on Enterprise...

See http://sqldev.net/misc/waittypes.htm for "CXPACKET"

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 4

this is turning into a real enigma ...

i wrote a set of queries to remove the paralellism and cut the execution time to 16 mins (from 20+ hours with some missing indexes)

when i ran the queries for the second time the last one just hangs again - the first time round it took 2 mins and the last time round it was still running after an hour. i killed the query and went back to the execution plan whihc i think is different.

i cannot understand why the second hash is needed ? anyone any ideas ?

also - maxseq is joined to SharePerformanceData000000038755 on an int value that is clustered in both tables yet the execution plan shows an index scan and not a clustered index scan as it did previously ...

Execution plan is:

|--Clustered Index Update(OBJECT[MBNATest].[dbo].[SharePerformanceData000000038755].[IX_SharePerformancetabAccId]), SET[SharePerformanceData000000038755].[tabUProcId]=[ms].[UProcId], [SharePerformanceData000000038755].[tabULoadId]=[ms].[ULoadId], [SharePerformanceData000000038755].[tabSequence]=[Expr1004], [SharePerformanceData000000038755].[tabAccBalance]=[ms].[tabAccBalance]))

|--Compute Scalar(DEFINE[Expr1004]=Convert([ms].[tabSequence])))

|--Top(ROWCOUNT est 0)

|--Hash Match(Aggregate, HASH[Bmk1000]), RESIDUAL[Bmk1000]=[Bmk1000]) DEFINE[ms].[tabSequence]=ANY([ms].[tabSequence]), [ms].[tabAccBalance]=ANY([ms].[tabAccBalance]), [ms].[ULoadId]=ANY([ms].[ULoadId]), [ms].[UProcId]=ANY([ms].[UProcId])))

|--Hash Match(Inner Join, HASH[ms].[AccountId])=([spd].[tabAccountId]), RESIDUAL[spd].[tabAccountId]=[ms].[AccountId]))

|--Clustered Index Scan(OBJECT[MBNATest].[dbo].[maxseq].[PK_maxseq] AS [ms]))

|--Index Scan(OBJECT[MBNATest].[dbo].[SharePerformanceData000000038755].[PK_SharePerformanceData000000038755] AS [spd]), ORDERED FORWARD)

cheers

dbgeezer