Could not continue scan with NOLOCK due to data movement


I have a SQL Server 2008 R2 database that has base data tables that are constantly being updated with merge statements (mostly updates, some inserts and deletes etc.) by a multi-threaded windows application that is constantly throwing new data at it.  

I have a stored procedure that selects from a view based on the data tables that are being updated and, because it does not matter if I get dirty reads (I would rather dirty read than creating locking issues), then I have included WITH(NOLOCK) table hint on the select from the view.  This seems to work fine the majority of the time but very occasionally I get the following error logged, "Could not continue scan with NOLOCK due to data movement".

The same error is also caused by another stored procedure that selects from the constantly changing data tables and merges the results with another table elsewhere.  I amended this procedure recently so that it selects the data WITH (NO LOCK) into a temp table and then merges the temp table with the other table, this was an attempt to grab the data as quickly as possible and not hold up the base data tables while I did my merge, and it did seem to reduce the errors I received greatly.

My question is, is this something to worry about or is it just that the data being searched has changed so much (split pages etc.? not sure what that means!) that it just couldn't get a result and I should catch the error in code and try to run the stored procedure again?

The stored procedures are run hundreds of times a day (maybe more) and I only get errors during times of very high volume (i.e. lots of data changing on base tables) and, even then, I only get 5 errors in a day maybe, just want to make sure I'm not missing something before I code a retry attempt.

Many thanks in advance

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you should write/structure your code to deal with the problem ... its an inherent possibilty when processing with nolock
in a DIRTY READ environment....

to mitigate the problem perhaps you should try and limit the scope of your selects ....

ie select top (10) ...

rather than trying to select all possible rows for your condition at once?

you perhaps need to re-visit your Unit of work strategy to ensure that impacts are minimised...
can these Reader applications afford to be delayed during High maintenance periods?

(ie on encountering the error sleep for a minute before continuing... or instead of selecting 100 rows only select 10...
 you'd need to build in some sort of control table to allow you to track and control this....

  i suppose it really depends on whether this problem appears to be increasing in frequency or is "stable" at this low level (5)
  occurrences per day....


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cp30Author Commented:

Thanks for your input.  One stored procedure is called every minute on a schedule as it updates some other tables, so as there's 7 similar procedures and they each get called every minute and out of all 7 only one has failed once on any day I think I will monitor and if it does not get any worse I can probably leave that as the next execution a minute later currently works fine.

The other stored procedure is actually a search triggered from the website which is limited to top 200 already, again that has been executed over 1000 times today and only threw that error on 2 separate occasions so I may add a check in the website error handling that if the error thrown is the NOLOCK scan issue that it retries once or twice and see if that subsequent attempt is successful.

Would appreciate your thoughts on whether this sounds like a sensible approach to take at this stage.

Many thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.