Risks of NOLOCK, part 1

Question

Risks of NOLOCK, part 1

Viewing 5 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

webrunner SSC-Dedicated Points: 32039 More actions · Answer 1

Hugo Kornelis (11/1/2015)
Comments posted to this topic are about the item <A HREF="/questions/Nolock/132977/">Risks of NOLOCK, part 1</A>

Thanks, Hugo. Great question.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

TomThomson SSC Guru Points: 104768 More actions · Answer 2

Kim Crosser (11/4/2015)
TomThomson (11/4/2015)
However, the use of NOLOCK still increases both the worker time and the elapsed time. These are the results I get:-
SP......... cached_time............exec count.. AvWorkerTime.. tot_wt...min_WT.. max_WT...AvElapsedTime. .tot_ET.. min_ET.. max_ET
axxL....2015-11-04 21:51:35.393... 26.............. 7585.. . . 197227... 7240....... 8092.... 7586....... 197249. . 7240…... 8092
axxN....2015-11-04 21:51:35.410... 26.............. 6660. .. . 173175... 6430. . . . 7036. . 6661....... 173192. . 6431…... 7037
Your results seem to show that the Nolock version (AxxN) is significantly lower than the Lock version for both worker and elapsed time.
Am I confused here?

No, you're not, I am - I misread the results of this version, got it completely upside down.

So now I'm going to spend a silly amount of time trying to work out why this change completely reversed the performance difference. :unsure:

Tom

Kim Crosser SSCommitted Points: 1763 More actions · Answer 3

I believe the initial results were an artifact of the "rollback". Unlike a truncate, which effectively removes the table data instantaneously, the rollback has to restore the state and clear the transaction buffers/logs.

There seem to be many cases where SQL Server will return control to a thread while the preceding step is still executing, so I think the initial times reflected that the "rollback" from the transaction with locking took longer to complete than the rollback from the "with (nolock)" transactions, so the procedure times included wait times (inside the timed procedure) for the preceding step to "really" complete. Thus, if the rollback from the "locking" transactions takes longer than the rollback from the "with (nolock)" transactions, the "with (nolock)" procedure times were inflated due to the extra rollback durations from the preceding step.

If my theory is correct, then if you were to stack 10 of the "with (nolock)" transactions together (including the begin and rollback), and then do 10 of the "locking" transactions in their own stack, you would see times indicating the nolocks were MUCH faster, as I believe the rollback overhead for the locking transactions is much higher.

Whenever possible, I like to use "truncate" (or even drop and recreate) to initialize tables for timing/performance testing.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

Interesting question, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

stephen.long.1 SSCrazy Points: 2591 More actions · Answer 5

Excellent question, Hugo, thanks. I got the first three right, but missed the performance one because I though that perhaps with NOLOCK, the system would choose a slightly sub-optimal query plan, but I'm probably wrong about that. Anyway, thanks, and I look forward to part 2! 🙂