SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Effect of NOLOCK on Performance


The Effect of NOLOCK on Performance

Author
Message
Wayne-153714
Wayne-153714
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1118 Visits: 91
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/WFillis/2764.asp


When in doubt - test, test, test!

Wayne

Dennis Rutten
Dennis Rutten
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 1

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


WesleyB
WesleyB
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 45

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


colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19577 Visits: 715

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 GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
AndrewMurphy
AndrewMurphy
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1784 Visits: 304

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.





colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19577 Visits: 715
it's a bit of a fallacy that you always need to clear the cache(s) , total of logical + physical should always be the same regardless of make up. In real terms you'd not spend time tuning queries which didn't remain in cache. clearing cache just gives worst duration/cpu io would be the same.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
noeld
noeld
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35282 Visits: 2051
I have to agree with colin here. This is one of those "dangerous" articles that could mislead people. You need to think really twice before using (NOLOCK) I would add that a hint is just that "a hint" not 100% of the time you will get what you asked for
Also it maybe safer to use READPAST instead of NOLOCK as the first option.

There are scenarios where it fits the bill but that should have been ( in my opinion ) the message that this article should have proposed.

Just My $0.02


* Noel
Andy Warren
Andy Warren
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Moderators
Points: 36978 Visits: 2762

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?



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
mtrevors
mtrevors
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 6

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?




Matt Trevors

B.CS(UNB),MCP,MCSE,MCSD .NET,MCDBA SQL 2K
DCPeterson
DCPeterson
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5023 Visits: 432

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.



/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search