0

I have a procedure that fills up a bunch of regular and temp tables with millions of records and takes hours to complete. Its no problem when I run it alone.

However, I've been trying to improve performance and am trying a SQL Profiler tuning trace. Now the procedure bombs every time with :

Could not continue scan with NOLOCK due to data movement.

If I turn off SQL Profiler it works again. My system is SQL R2 64 SP2 on a Dell Precision T5400 quad Xeon, 8gb RAM and plenty of storage capacity (3tb) on W2k8 Server 64 (latest sp).

Tom McDonald
  • 1,532
  • 2
  • 18
  • 37
  • Also, the server is isolated. No other DB is running and the only activity is this procedure. There are no outside connections--just this proc. – Tom McDonald Oct 03 '13 at 14:16
  • SQL Profiler changes timing significantly and may expose a race condition which you have *anyway*. The exception you see is to be expected if you use NOLOCK in the procedure. Try using `sp_trace_xxx` server side tracing instead of SQL Profiler. Very few perf problems require SQL Profiler anyway, there are far better tools in the DMVs/wait_stats. – Remus Rusanu Oct 03 '13 at 14:34
  • I don't use NOLOCK in the procedures. Does that change your suggestion? – Tom McDonald Oct 03 '13 at 16:45

1 Answers1

0

The solutions to this query was to reduce the complexity of the query. It was inserting millions of records in one big load. I broke it into several smaller loads and this error went away.

Tom McDonald
  • 1,532
  • 2
  • 18
  • 37