Justify NOT using (nolock) hint in every query

Have you ever had to justify NOT using a query hint?

I am seeing WITH (NOLOCK) in every single query that hits a very busy server. It is to the point that the developers think it should just be on by default because they hate seeing it in their code thousands of times.

I tried to explain that it is allowing dirty reads and they will end up with bad data eventually, but they believe the performance tradeoff is well worth it. (Their database is a mess; no wonder they have performance issues.)

If you have a clear example of how to present the case against this abuse of the NOLOCK hint, that would be appreciated.

You pick your battles and battles like this can't be easily won. We have a system where every DML is hinted with the ROWLOCK hint (irrespective of modifying one row or several thousand rows). I showed several examples why it really hurts performance but as the system is already working, there is resistance to change. Note that I convinced them enough NOT to use this going forward though.

NOLOCK has it place but I can recommend some good references showcasing the troubles of using it:

• Microsoft SQL Server Development Customer Advisory Team blog – Previously committed rows might be missed if NOLOCK hint is used

• Itzik Ben Gan at SQL Magazine – Clustered Index Scans Part III

• Itkiz Ben Gan on sqlpass.org – Beware the NOLOCK hint

StackExchange Database Administrators Q#2684, answer score: 17