Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

The Effect of NOLOCK on Performance Expand / Collapse
Author
Message
Posted Friday, December 15, 2006 9:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:01 AM
Points: 143, Visits: 62
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/WFillis/2764.asp


When in doubt - test, test, test!

Wayne

Post #330889
Posted Wednesday, January 24, 2007 2:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 30, 2007 6:04 AM
Points: 1, 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

Post #339261
Posted Wednesday, January 24, 2007 3:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, May 2, 2011 5:35 AM
Points: 493, 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

Post #339268
Posted Wednesday, January 24, 2007 5:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697

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/
Post #339281
Posted Wednesday, January 24, 2007 6:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:04 AM
Points: 278, 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.




Post #339303
Posted Wednesday, January 24, 2007 8:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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/
Post #339370
Posted Wednesday, January 24, 2007 8:36 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 6,259, Visits: 2,031
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
Post #339377
Posted Wednesday, January 24, 2007 9:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, November 17, 2014 2:20 PM
Points: 6,800, Visits: 1,914

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
Post #339387
Posted Wednesday, January 24, 2007 9:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 21, 2009 5:27 PM
Points: 76, 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
Post #339397
Posted Wednesday, January 24, 2007 9:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:18 PM
Points: 1,035, Visits: 411

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



*****************/
Post #339399
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse