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 ««123»»

NOLOCK Discussion Expand / Collapse
Author
Message
Posted Thursday, September 04, 2008 1:50 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 03, 2012 7:36 AM
Points: 127, Visits: 517
It is my understanding that nolock only helps when you have a write lock on a row/table. C# has something simular for accessing objects in a multithreaded environment. You can have as many 'read locks' as you want, but as soon as a 'write lock' comes in, your read locks wait until everything is commited. By putting in the nolock on a read, you by-pass the 'wait for clean data' and just read as-is.

If I'm wrong, let me know, because I thought this is how it worked.

edit: does a query have to check every row to see if it's locked or does nolock only speed up a fixed check then any waiting on writes?

eg. hypothetically, would a table that will never get written to see a scaling performance increase or a fixed; is a lock a one time check or does it have to be checked per row, every single time?
Post #564141
Posted Thursday, September 04, 2008 1:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 05, 2012 9:52 AM
Points: 86, Visits: 103
That's what I thought as well... So except for where exact reporting is required, if you do your job right on the SQL syntax and DB design a NOLOCK statement can only help in CRM environments...

Thats where my experience is, I have not dealt with extremely high transaction enviroments.
Post #564142
Posted Thursday, September 04, 2008 2:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 7,090, Visits: 14,717
bcronce (9/4/2008)
It is my understanding that nolock only helps when you have a write lock on a row/table. C# has something simular for accessing objects in a multithreaded environment. You can have as many 'read locks' as you want, but as soon as a 'write lock' comes in, your read locks wait until everything is commited. By putting in the nolock on a read, you by-pass the 'wait for clean data' and just read as-is.

If I'm wrong, let me know, because I thought this is how it worked.

edit: does a query have to check every row to see if it's locked or does nolock only speed up a fixed check then any waiting on writes?

eg. hypothetically, would a table that will never get written to see a scaling performance increase or a fixed; is a lock a one time check or does it have to be checked per row, every single time?


I think Grant hit on the point pretty well. If it DOES help that much, then there's some other underlying issue you're glossing over.

To me NOLOCK is the equivalent of a medic treating someone, saying:"Now I'm going to ignore the gunshot wound you have, and just fix everything ELSE, okay?"


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #564152
Posted Thursday, September 04, 2008 2:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 41,572, Visits: 34,500
Honestly, in my opinion, if you do your job right on the SQL syntax, DB design and indexing a NOLOCK statement shouldn't be necessary in any environment.

If you're encountering severe locking problems in SQL 2005 or higher, use SNAPSHOT isolation. That way reads never take locks, but don't read inconsistent data. It's the way Oracle works (more or less). The downside is that TempDB usage increases.

Yes, there will be the occasional case where nolock is necessary. The problem is that it is vastly overused with people adding it to every query without the slightest knowledge of what it actually means or the possible problems they could be causing for themselves.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #564161
Posted Thursday, September 04, 2008 2:22 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
NOLOCK does have its uses! The main problem is that many tend to abuse it or use it where is not really appropriate.




* Noel
Post #564167
Posted Friday, September 05, 2008 8:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 10:04 AM
Points: 15,442, Visits: 9,590
I might see Nolock or Readpast as moderately useful in a Where Exists statement in a trivial issue. Even there, I'd be concerned about it, since it's hard to tell if something "trivial" will turn into something seriously non-trivial somewhere down the road.

I have used index hints a few times, mainly in testing. Add five or six indexes to a table, force certain queries to use each one, test IO and CPU time and such, decide which one is best and then get rid of the other indexes and the hints.

A very few times, I've found that index and join hints have helped, when one proc might return a small number of rows some of the time, and a huge number other times, and code maintenance precludes separate procs with separate execution plans. I'd rather run an unnecessary hash join on a small data set in order to make the large data set run that one instead of a nested loop.

I have one proc, for example, that runs 5-10 rows half the time, and over 10-million rows the other half of the time, up to 53-million rows about 10% of the time. I tested recompile vs forcing a hash join and found the hash join hint ended up with the best average performance. Also had to force certain indexes, also based on average performance testing. Pushed the small data sets up to about 1 second total processing time, up from half a second, but moved the large data sets down to 18 minutes from 3+ hours, and the huge data sets down to 1-2 hours from up to 3 days. In that case, after extensive testing, I'm using the hints I came up with. But that's an extreme case. (This is an automated process on a critical workflow. There isn't a user sitting there waiting for a web page to finish loading or anything like that.) And there isn't a single Nolock in that process, since it absolutely cannot have dirty reads for business reasons.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #564623
Posted Friday, September 05, 2008 3:29 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
Just for an example I had to find some "bad" dates (.... "24:00:00") that crippled a some random rows in a large table (500+ million rows) and locking the table was simply not an option, lock escalation was prohibitive and creating an index was not possible... "Nolock" to the rescue!


* Noel
Post #564928
Posted Monday, September 08, 2008 5:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 14,840, Visits: 27,315
I remain where I was. Any of the hints can be useful in a given situation. My primary issue is when people see it as a general fix or best practice for lots of places or even everywhere. I've seen it done with various hints, NOLOCK, FAST N, KEEP FIXED PLAN... Every one one of them appeared to "fix" the underlying problem, bad code, bad indexing, bad table design, in the short term. Every one of them, when applied universally, proved to be extremely costly in the long run. NOLOCK starts returning bad data. Duplicate rows, in any system, are an issue. As are missing rows. Both these can be cased by NOLOCK. It's not just a question of getting 'NY' instead of 'New York' because an update is in progress.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #565310
Posted Tuesday, September 09, 2008 3:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 9:15 AM
Points: 44, Visits: 148
The one place where I frequently use with (nolock) is in our datawarehousing ETLs - I KNOW that nothing else is hitting my staging database while my ETL is loading - I'm writing, then I'm reading.
If anything else is hitting the database, I have bigger problems.

Also MAXDOP - theoretically SQL knows best, but I've come across many instances where the parallelisation that it performs is nowhere near optimal.
Post #565990
Posted Tuesday, September 09, 2008 6:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 14,840, Visits: 27,315
Yeah, MAXDOP has been useful situationally. Although if I see a lot of queries suffering from parallelism, I'll raise the threshold.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #566073
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse