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
create proc dbo.axxL as
update #temp set t = null where t in (select p from prime) and t in (select c from composite)
if exists (select 1 from sys.procedures where name = 'axxN' and schema_id = 1) drop proc axxN
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))
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.
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.