Behind the scenes, the queries themselves behave the same.
After discussing with some colleagues, I can summarize that the differences
you see are due to:
(a) system tables being accessed during query compilation and plan caching
(b) perhaps a query plan cached for one version and not the other (the
nolock version likely isn't cached)
(c) the procedure cache being empty
In other words, the locks on intNumVisitors are the same in both cases, but
different situations can cause locks to occur on other objects and while
managing the procedure cache, and because the lock messages are cryptic and
don't reference tables directly, it can be misleading. Much of this will
not affect performance, for example a lot of the procedure cache work is
performed as a result of the query, not before the query.
I ran these queries multiple times in Query Analyzer, against 8.00.2040, and
there didn't seem to be much difference between the locks acquired. I
didn't cross-reference everything, but I would put money on the notion that
not all of the locks are against the Orders table.
use Northwind
go
dbcc freeproccache
dbcc traceon(1200,3604)
go
select * from Orders with (nolock)
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (5200bb3a0129) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (ed00743f3a5a) (class bit0 ref1)
result: OK
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Process 60 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 1:36:1 (ed00743f3a5a) (class bit0 ref1)
result: OK
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Process 60 acquiring S lock on KEY: 6:1:2 (260178426497) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 6:1:1 (cb0045aaeb0b) (class bit0 ref1)
result: OK
Process 60 acquiring Schema lock on TAB: 6:21575115 [] (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 6:3:1 (cc0073e4c1ea) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (cc0073e4c1ea)
Process 60 acquiring S lock on KEY: 6:3:1 (cd009d4b74f8) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (cd009d4b74f8)
Process 60 acquiring S lock on KEY: 6:3:1 (ce00f82cc840) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (ce00f82cc840)
Process 60 acquiring S lock on KEY: 6:3:1 (cf0041141fdd) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (cf0041141fdd)
Process 60 acquiring S lock on KEY: 6:3:1 (d0002473a365) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d0002473a365)
Process 60 acquiring S lock on KEY: 6:3:1 (d100cadc1677) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d100cadc1677)
Process 60 acquiring S lock on KEY: 6:3:1 (d200afbbaacf) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d200afbbaacf)
Process 60 acquiring S lock on KEY: 6:3:1 (d300f9abc997) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d300f9abc997)
Process 60 acquiring S lock on KEY: 6:3:1 (d4009ccc752f) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d4009ccc752f)
Process 60 acquiring S lock on KEY: 6:3:1 (d5007263c03d) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d5007263c03d)
Process 60 acquiring S lock on KEY: 6:3:1 (d60017047c85) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d60017047c85)
Process 60 acquiring S lock on KEY: 6:3:1 (d700ae3cab18) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d700ae3cab18)
Process 60 acquiring S lock on KEY: 6:3:1 (d800cb5b17a0) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:3:1 (d800cb5b17a0)
Process 60 acquiring S lock on KEY: 6:3:1 (d90025f4a2b2) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 6:1:1 (cb0045aaeb0b) (class bit0 ref1)
result: OK
Process 60 acquiring S lock on KEY: 6:2:1 (cc001ccb16a2) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (cc001ccb16a2)
Process 60 acquiring S lock on KEY: 6:2:1 (cd00df983b89) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (cd00df983b89)
Process 60 acquiring S lock on KEY: 6:2:1 (ce009ea92090) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (ce009ea92090)
Process 60 acquiring S lock on KEY: 6:2:1 (cf00593f61df) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (cf00593f61df)
Process 60 acquiring S lock on KEY: 6:2:1 (d000180e7ac6) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (d000180e7ac6)
Process 60 acquiring S lock on KEY: 6:2:1 (d100db5d57ed) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (d100db5d57ed)
Process 60 acquiring S lock on KEY: 6:2:1 (d2009a6c4cf4) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (d2009a6c4cf4)
Process 60 acquiring S lock on KEY: 6:2:1 (d3005570d473) (class bit0 ref1)
result: OK
Process 60 releasing lock on KEY: 6:2:1 (d3005570d473)
Process 60 acquiring S lock on KEY: 6:2:1 (d4001441cf6a) (class bit0 ref1)
result: OK
Process 60 releasing lock on TAB: 6:21575115 []
Process 60 acquiring Schema lock on TAB: 6:21575115 [] (class bit0 ref1)
result: OK
(830 row(s) affected)
Process 60 releasing lock on TAB: 6:21575115 []
use Northwind
go
dbcc freeproccache
dbcc traceon(1200,3604)
go
set transaction isolation level read uncommitted
select * from Orders
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:30:1 (f50189d99431) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (5200bb3a0129) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (ed00743f3a5a) (class bit0 ref1)
result: OK
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Process 61 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (9b00449987ba) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 1:36:1 (ed00743f3a5a) (class bit0 ref1)
result: OK
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Process 61 acquiring S lock on KEY: 6:1:2 (260178426497) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 6:1:1 (cb0045aaeb0b) (class bit0 ref1)
result: OK
Process 61 acquiring Schema lock on TAB: 6:21575115 [] (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 6:3:1 (cc0073e4c1ea) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (cc0073e4c1ea)
Process 61 acquiring S lock on KEY: 6:3:1 (cd009d4b74f8) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (cd009d4b74f8)
Process 61 acquiring S lock on KEY: 6:3:1 (ce00f82cc840) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (ce00f82cc840)
Process 61 acquiring S lock on KEY: 6:3:1 (cf0041141fdd) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (cf0041141fdd)
Process 61 acquiring S lock on KEY: 6:3:1 (d0002473a365) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d0002473a365)
Process 61 acquiring S lock on KEY: 6:3:1 (d100cadc1677) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d100cadc1677)
Process 61 acquiring S lock on KEY: 6:3:1 (d200afbbaacf) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d200afbbaacf)
Process 61 acquiring S lock on KEY: 6:3:1 (d300f9abc997) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d300f9abc997)
Process 61 acquiring S lock on KEY: 6:3:1 (d4009ccc752f) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d4009ccc752f)
Process 61 acquiring S lock on KEY: 6:3:1 (d5007263c03d) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d5007263c03d)
Process 61 acquiring S lock on KEY: 6:3:1 (d60017047c85) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d60017047c85)
Process 61 acquiring S lock on KEY: 6:3:1 (d700ae3cab18) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d700ae3cab18)
Process 61 acquiring S lock on KEY: 6:3:1 (d800cb5b17a0) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:3:1 (d800cb5b17a0)
Process 61 acquiring S lock on KEY: 6:3:1 (d90025f4a2b2) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 6:1:1 (cb0045aaeb0b) (class bit0 ref1)
result: OK
Process 61 acquiring S lock on KEY: 6:2:1 (cc001ccb16a2) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (cc001ccb16a2)
Process 61 acquiring S lock on KEY: 6:2:1 (cd00df983b89) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (cd00df983b89)
Process 61 acquiring S lock on KEY: 6:2:1 (ce009ea92090) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (ce009ea92090)
Process 61 acquiring S lock on KEY: 6:2:1 (cf00593f61df) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (cf00593f61df)
Process 61 acquiring S lock on KEY: 6:2:1 (d000180e7ac6) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (d000180e7ac6)
Process 61 acquiring S lock on KEY: 6:2:1 (d100db5d57ed) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (d100db5d57ed)
Process 61 acquiring S lock on KEY: 6:2:1 (d2009a6c4cf4) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (d2009a6c4cf4)
Process 61 acquiring S lock on KEY: 6:2:1 (d3005570d473) (class bit0 ref1)
result: OK
Process 61 releasing lock on KEY: 6:2:1 (d3005570d473)
Process 61 acquiring S lock on KEY: 6:2:1 (d4001441cf6a) (class bit0 ref1)
result: OK
Process 61 releasing lock on TAB: 6:21575115 []
Process 61 acquiring Schema lock on TAB: 6:21575115 [] (class bit0 ref1)
result: OK
(830 row(s) affected)
Process 61 releasing lock on TAB: 6:21575115 []