Justify NOT using (nolock) hint in every query

Question

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.

Answer 1

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

Answer 2

It's been discussed on SO before:

• https://stackoverflow.com/questions/2141104/using-nolock-hint-in-ef4/2141802#2141802
• https://stackoverflow.com/questions/1682240/what-can-happen-as-a-result-of-using-nolock-on-every-select-in-sql-sever

Define very busy. We have high volumes (50k new rows per second, large aggregates etc) and don't see the need to get dodgy data too

Answer 3

You have to explain to your colleagues the importance of understanding the isolation levels. Show them examples. The nicest and easiest explanation i found on Little Kendra's poster of isolation levels. Ask them why they think they need nolock hint. Why don't they use the "set transaction isolation level ..." statements? Ask what exactly is the situation they want to fix, maybe they have deadlocks, blocking..etc. If they just don't want to hold locks they might consider snapshot isolation level.

Only by asking them you can have a clear picture.