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