We have noticed the following error message periodically during mass data update on one of our productions servers: ‘Error 601: Could not continue scan with NOLOCK due to data movement’. This server is hosting instance of SQL Server 2008R2 SP1 Standard edition. After some research we have found a detailed description of this issue in this KB article: http://support.microsoft.com/kb/960770/en-us.
In our case the error was generated from a code similar to this pattern:
DECLARE @TempTable TABLE (...) INSERT INTO @TempTable SELECT * FROM .... INSERT INTO @TempTable select * from ( select distinct * from @TempTable ) tt cross join dbo.TableA a where a.Col1 >= 1
The issue is that data are inserted into @TempTable at the same time when they are retrieved. Writing into this table is causing the reallocation of data pages which should first be read and at the time of reading they aren’t located at the expected place.
A simple and intuitive solution is code refactoring: Data are inserted into #Distinct temporary table first and after that used to prepare the final data to be inserted into @TempTable. This way we can prevent error to be fired.
DECLARE @TempTable TABLE (...) INSERT INTO @TempTable SELECT * FROM .... SELECT DISTINCT * INTO #Distinct FROM @TempTable INSERT INTO @TempTable SELECT * FROM #Distinct tt cross join dbo.TableA a WHERE a.Col1 >= 1 GO