CXPACKET wait type on Linked Server Operation
-
Hello there, we have two servers:
SQL 2000 sp4
SQL 2005 SP2
We have a SP on the 2000 box that has worked fine for years, last night we installed SP4 and this job is now failing. Specifically:
We run the following sp on the 2000 server:
DECLARE @StartPeriod as DATETIME,@EndPeriod as datetime
set @EndPeriod = Convert(datetime,GetDate(),103)
set @StartPeriod = DateAdd([Day],-200,GetDate())
/*Delete data to be overwritten*/
delete from tblPickerReport
Where InvoiceDate > @StartPeriod
/*Create starting data from Picker Hours*/
INSERT INTO dbo.tblPickerReport
SELECT Period, Convert(DATETIME, InvoiceDate, 103) AS InvoiceDate, Picker, Orders, Lines, Hours, '0', '0','0','0'
FROM dbo.vwPICKER_HOURS (NOLOCK)
Where Convert(DATETIME, InvoiceDate, 103) > @StartPeriod
Order By InvoiceDate
/*Insert number of credit orders received by picker*/
UPDATE tblPickerReport
SET tblPickerReport.COrders = DerivedvwCOHCredLink.Orders
FROM dbo.tblPickerReport tblPickerReport
INNER JOIN ( SELECT TOP 100 PERCENT
COUNT(DISTINCT DOC_NUMBER) AS Orders,
OPERATOR,
CONVERT(DATETIME, Expr1, 103) AS Date
FROM dbo.vwCOHCredLink
GROUP BY OPERATOR,
CONVERT(DATETIME, Expr1, 103)
HAVING ( CONVERT(DATETIME, Expr1, 103) > CONVERT(DATETIME, @StartPeriod, 103) )
) DerivedvwCOHCredLink ON tblPickerReport.InvoiceDate = DerivedvwCOHCredLink.[Date]
AND tblPickerReport.Picker = DerivedvwCOHCredLink.[Operator]
The delete and insert statements work fine, however the UPDATE problem gets stuck on the 2000 box with WAIT Resource ('SQL2005 BOX SPID=124').
When we dial into the 2005 box, PID 124 is suspended with WAIT TYPE CXPACKET. the PID also has 4 other threads open with no command.? Screenshot below shows PID 124's thread and last command:
NOTE the command is being run on master? i'm guessing this is a background operation our 2000 box is sending? "FETCH API_CURSOR0000000000000826"?!?!?!?
WE are aware of maxdop for setting the max for multiple paralellised processed, but with the 'FETCH API_CURSOR0000000000000826' command not being sent/generated by us i dont see how we can sort this!?!?!?
Any advice would be awesome! as we are totally stumped!
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3.... -
UPDATE:
as a test i tried to disable parallelism by running the following:
exec sp_configure 'max degree of parallelism', 1
go
reconfigure
go
THe SP still gets stuck on the 2005 box, however the thread is no longer suspended, but is just stuck on 'FETCH API_CURSOR0000000000000AC0'..... (used to take seconds)
EDIT: Another point of interest, when we run the sub-select used in the update query seperatly, it selects data fine.... Now we are really confused
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply