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?

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?

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.

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.

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!

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 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.