Hi Wayne,

Nice article!

I would be interested to know if there is a linear relation between the performance gain using NOLOCK and the number of joins

performed in the query. I suspect that you might see that the effect increases with the number of joins. Also , the effect

is probably greatest in a busy database where there might be a problem with lock waits - but this would be difficult to test in a

controlled environment.

Dennis

Hi,

Have you tried running the queries in a different order (first the NOLOCK variant and then the normal one)?  And running them with MAXDOP 1?

Anyway, there is a little catch with NOLOCK however.  SQL Server mostly uses unordered clustered index scans when NOLOCK is specified, this can cause duplicate records to be returned when page splits occur while your query is scanning the index.

Kind regards,
Wesley

sorry but i'm totally unconvinced of the results and I personally think this is probably a bad article to be published as it may misslead.

Isolation levels are critical to data intergrity and as mentioned above scans with dirty reads are capable of returning duplicated data where duplicates cannot exist - Itzik Ben-Gan demonstrated this to the UK SQL Server User Group last year, if you check out Tony Rogerson's blog he has code which demonstrates this. http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx

You have not run these tests on a server, sorry but a laptop does not count! I did run a few comparisions on a large clustered server and as far as query plans and io stats from QA the nolock hint made no difference. I couldn't find any queries slow enough to measure time diffs , but I accept that different locking strategies may well impact duration. Possibly running this to compare stats in profiler would give a better indication. Where there is contention ( most oltp apps ) then you probably would make some gains on a table scan, but hopefully you wouldn't want to do table scans anyway.

Your query is table scanning, which will pretty well always invoke parallelism as the query cost for a table scan on a largish table does this. I agree with Wesley, you should have done this with maxdop specified.

Please please avid posters who have read this article check out the effects of dirty reads and isolation levels before you start adding nolock hints all through your code as a tuning aid, it's not and should not be used as such unless you really really don't care if your data may be incorrect.

On the matter of testing it is vital to test on equal o/s, sql editions and some measure of compatible hardware  as a production environment.

The issue of CACHE affecting the runtime's + physical read etc also needs to be considered.  True testing would involve using the DBCC DROPCLEANBUFFERS + DBCC FREEPROCCACHE commands between runs.

I'd also agree w.r.t. seeing results from a system where "test runs" are being subjected to outside influences such as other users.

I havent been the contrarian lately, so....

I tend to use NOLOCK frequently, but I don't write financial apps and I generally understand the data movement that might occur. I think a comparison of READPAST vs NOLOCK would be interesting indeed.

I think the usefulness varies and I would have guessed at maybe a 5% savings. Maybe its the same thing said a different way, but I use NOLOCK because I know that I dont need a lock and dont want to risk blocking writers, and I dont want to be blocked - which I guess translates to a performance boost because I shouldn't be waiting or causing waits, but I don't use it to get a x% decrease in resource usage (though maybe I should?).

Couple thoughts from that. One, can we craft a test that would satisfy us all, and then maybe see if the author or someone else is game to dig in deeper? Two, maybe we can get Steve to lean on MS to see if they can explain a bit about the performance implications.

READPAST has me thinking - should I be using it more?

I agree, somewhat, with both sides of the argument on this one... NOLOCK is useful; I've used it to help tweak our internal system here where people were needlessly suffering from blocking.   The concern about misleading people is true as I believe a well written system should not require the extensive use of NOLOCK; but, I'm assuming that to be mislead you have to be a novice with SQL server.  If you're a novice with SQL server you probably don't even know what an isolation level is, or that the default isolation level is read commited which will indeed benefit from the use of NOLOCK in certain, CAREFULLY CONSIDERED, situations.

There's my 2 cents.... I'm Canadian, so does that make mine less valuable?

I second Collin here.  NOLOCK is fine for some things (as Andy points out).  However, it can be dangerous too and one MUST understand the tradeoffs involved before you use it.  This article didn't even hint at the tradeoffs.

I would suggest that if you really need non-interference between "readers" and "writers" you should use the Read Committed Snapshot Isolation Level available in 2005.  It at least doesn't allow unexpected duplicates and dirty reads.

If eliminating overhead is the prime motivation, you shouldn't be using a Relational DBMS.  File based storage mechanisims are far more "efficient" for churning through large data sets.  There is a lot of overhead imposed by things like transactional and logical consistency, but for most applications the costs of ignoring these things is far greater.  The problem is that those costs are often not immediately apparent.  You certainly can't write a simple article that purports to show a percentage "improvement" -or loss - of logical correctness...

Using NOLOCK indiscriminately WILL bite you, your employer, and your customers eventually and it will bite harder than you might think.