Lately, I have been getting this problem with a client. The query returns
around 6 thousand records, there is one user only and it's a select statement
with a temporary table in the join.
The server has a sp4 installed. It's a 2003 server.
CREATE TABLE #Temp (Temp_Id INT,
Temp_Id_Count INT NULL,
Temp_Id_Dt DATETIME NULL)
INSERT INTO #Temp (Temp_Id, Temp_Id_Count, Temp_Id_Dt)
SELECT T.Temp_Id, COUNT(TR.Temp_Id_Count), MAX(TR.Temp_Id_Dt)
FROM Test T WITH (NOLOCK)
LEFT JOIN Test_History TR WITH (NOLOCK) ON TR.Temp_Id = T.Temp_Id
WHERE T.Status_Cd = 1234
GROUP BY T.Temp_Id
SELECT DISTINCT T.Temp_Id, S.Client_Id, S.Subject_Id,
FROM SavedData S WITH (NOLOCK)
JOIN #Temp T ON T.Temp_Id = S.Temp_Id
The error after a few seconds is 601 - Could not continue scan with NOLOCK
due to data movement.
Thanks in advance