Sean Lange (6/30/2015)

---

Jacob Wilkins (6/30/2015)

---

This is all rather interesting, so I thought I'd chime in.

Based on what I've tested, Scott is technically correct. Under the right conditions, even where it is not preventing blocking, NOLOCK can be faster than a query without the hint.

Now, that's very hard to measure for two reasons.

First, the overhead of locking is very, very small. On the lab machine I most recently tested, it usually works out to around .4 microseconds per lock. So, unless you're taking out millions of locks, it's very easy for the locking overhead to be washed out by normal run-to-run variance in results.

If I run Jeff's sample (with a regular table, not a temp table, to avoid that confounding factor) I only end up with about 10000 pages. Even if running the query took out all 10000 page locks (which it doesn't, and that will bring us to the second reason it's hard to measure this), that's about 4 milliseconds, which is easily missed because of run-to-run variance.

Second, because of lock escalation, you're not even going to see that 4 ms difference. After the first 5000 pages are locked, the process will just escalate to a table lock if it can, so now we're down to about 2 ms difference. That's going to be easily wiped out by normal variance in duration.

If you disable lock escalation, and then use a ROWLOCK hint, you can start to measure the difference if you have enough rows. I've updated Jeff's sample to illustrate all this.

--DROP TABLE perftest;

GO

--===== Create and populate a heap on the fly

SELECT TOP 1000000 ac1.*

INTO perftest

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

GO

--===== Prime the pump just to make everything even for both tests

DECLARE @Bitbucket SYSNAME;

SELECT @Bitbucket = [name]

FROM perftest;

GO

--===== REPEATABLE READ will let us run the select within a transaction,

--===== and since the locks will be held until we rollback, we can see

--===== what locks were taken out

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--===== This is the WITH (NOLOCK) code

BEGIN TRANSACTION

DECLARE @Bitbucket SYSNAME;

SET STATISTICS TIME,IO ON;

SELECT @Bitbucket = [name]

FROM perftest WITH (NOLOCK);

SET STATISTICS TIME,IO OFF;

SELECT COUNT(*), request_type, request_mode, resource_type

FROM sys.dm_tran_locks

WHERE request_session_id=@@SPID

GROUP BY request_type,request_mode,request_owner_type, resource_type

ROLLBACK

--===== This is the un-hinted code

BEGIN TRANSACTION

DECLARE @Bitbucket SYSNAME;

SET STATISTICS TIME,IO ON;

SELECT @Bitbucket = [name]

FROM perftest

SET STATISTICS TIME,IO OFF;

SELECT COUNT(*), request_type, request_mode, resource_type

FROM sys.dm_tran_locks

WHERE request_session_id=@@SPID

GROUP BY request_type,request_mode,request_owner_type, resource_type

ROLLBACK

--Now disable lock escalation and repeat the above

ALTER TABLE perftest SET (LOCK_ESCALATION=DISABLE)

--===== This is the WITH (NOLOCK) code

BEGIN TRANSACTION

DECLARE @Bitbucket SYSNAME;

SET STATISTICS TIME,IO ON;

SELECT @Bitbucket = [name]

FROM perftest WITH (NOLOCK);

SET STATISTICS TIME,IO OFF;

SELECT COUNT(*), request_type, request_mode, resource_type

FROM sys.dm_tran_locks

WHERE request_session_id=@@SPID

GROUP BY request_type,request_mode,request_owner_type, resource_type

ROLLBACK

--===== This is the un-hinted code

BEGIN TRANSACTION

DECLARE @Bitbucket SYSNAME;

SET STATISTICS TIME,IO ON;

SELECT @Bitbucket = [name]

FROM perftest

SET STATISTICS TIME,IO OFF;

SELECT COUNT(*), request_type, request_mode, resource_type

FROM sys.dm_tran_locks

WHERE request_session_id=@@SPID

GROUP BY request_type,request_mode,request_owner_type, resource_type

ROLLBACK

--===== Now let's force row level locks. We should see a noticeable difference in runtime now.

--===== At .4 microseconds, a million locks should add about 400 milliseconds to the duration

--===== of the query.

BEGIN TRANSACTION

DECLARE @Bitbucket SYSNAME;

SET STATISTICS TIME,IO ON;

SELECT @Bitbucket = [name]

FROM perftest WITH (ROWLOCK)

SET STATISTICS TIME,IO OFF;

SELECT COUNT(*), request_type, request_mode, resource_type

FROM sys.dm_tran_locks

WHERE request_session_id=@@SPID

GROUP BY request_type,request_mode,request_owner_type, resource_type

ROLLBACK

So, yes, there is some overhead, but under any normal circumstances it won't even be measurable. You actually have to force SQL Server to behave in the worst way possible (lock escalation disabled and force row locks) to reliably measure it.

In practice, then, any speed increases will be solely due to avoiding blocking, and with that comes with all the usual problems of NOLOCK. To be fair, we already knew that, though 🙂

Cheers!

So let me summarize in my own words...

Using NOLOCK can actually boost performance but on any tables less than hundreds of millions of rows the performance is so small it is nearly immeasurable. On the other hand, there are dozens and dozens of articles that go into great detail about the pitfalls and downsides of using NOLOCK with as few as a thousand rows.

Seems to me that sort of suggests that hint has no real usage in almost every situation.