Risks of NOLOCK, part 1

Question

Risks of NOLOCK, part 1

Viewing 7 posts - 31 through 36 (of 36 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Kim Crosser (11/3/2015)
Thank you for all the work here, but I think the rollbacks and "update" transactions in your test scenario may be creating some of the difference.

That the rollbacks have any effect at all struck me as very unlikely, since the system view refers to time inside the procs and the begin tran and rollback statements are outside the procs. And one should normally expect reads to occur in a context where there are writes - unless the result of the read is just to be displayed, so if the updates do have some effect it's something that should be measured anyway.

So I decided to run a test to check whether in fact the transactions, rollbacks, and updates were doing something strange to the times. To do this I replaced the two SPs by two different SPs defined as follows:-

if exists (select 1 from sys.procedures where name = 'axxL' and schema_id = 1) drop proc axxL

go

create proc dbo.axxL as

update #temp set t = null where t in (select p from prime) and t in (select c from composite)

go

if exists (select 1 from sys.procedures where name = 'axxN' and schema_id = 1) drop proc axxN

go

create proc [dbo].[axxN] as

update #temp set t = null where t in (select p from prime(nolock)) and t in (select c from composite(nolock))

go

Clearly these SPs never do an update, since no numbers are both composite and prime, so there's no need for the rollback statements and hence no need for the begin tran statements.

So I commented out all the begin tran and rollback statements.

That makes the workload absolutely trivial.

However, the use of NOLOCK still increases both the worker time and the elapsed time. These are the results I get:-

edit: I don't know how I misread the results badly enough to say that. The simplified workload makes NOLOCK deliver better

performance. But how or why it manages to reduce the NOLOCK case by more than the LOCK case iseems completely incomprehensible

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

I do believe there are *interesting* scenarios where the "with (nolock)" could potentially slow down the SQL engine compared to the default locking, but I suspect that even there the difference will be far less than then 1-2% as originally stated.

Yes, there do appear to be interesting scenarios where nolock slows things down. But the results above with no explicit transactions, no rollbacks, and actual writes suggest that such scenarios can be extremely simple.

(Edit)
BTW - for fun, I also rand these joining the Prime values to the "ix" column (the primary key).
This was even more in favor of the (nolock) - the (nolock) queries ran at 81.78% of worker time, and 81.86% of elapsed time.

The composite table is quite useful when used together with the prime table to create filters that are always true (on positive smallint values) or always false (when used with any numeric type) while leaving the optimiser unable to predict it; of course it's not the only way to do that, and it has other uses too.

Tom

Kim Crosser SSCommitted Points: 1763 More actions · Answer 2

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?

webrunner SSC-Dedicated Points: 31863 More actions · Answer 3

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: 104773 More actions · Answer 4

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 5

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: 259105 More actions · Answer 6

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: 2582 More actions · Answer 7

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! 🙂