Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
There was a comment somewhere on our site regarding NOLOCK: it isn't SQL Server's turbo button. I completely agree with it! :-) –  Marian May 12 '11 at 20:55
add comment

3 Answers 3

up vote 14 down vote accepted

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:

share|improve this answer
4  
is this Itzik's article? He's saying there: "* Be aware of the implications of reading data with the NOLOCK hint (or in a read uncommitted isolation). It’s not just a matter of reading uncommitted changes, or data in an intermediate state of the transaction; rather you might even get the same row twice, or SQL Server might lose the scan position during the scan." –  Marian May 12 '11 at 21:04
    
Hi Marian, Yes. I saw a video of him on SQLPass.org website showing that in few demo's among other issues. –  Sankar Reddy May 12 '11 at 21:10
    
@Marian's link is the first link Sankar gave... –  ErikE Feb 12 '12 at 22:47
    
@ErikE: sorry, I don't remember all the discussion flow. I may have missed Sankar's link.. –  Marian Feb 24 '12 at 16:39
add comment

It's been discussed on SO before:

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

share|improve this answer
add comment

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.

share|improve this answer
4  
They just think it makes it go faster. –  datagod May 13 '11 at 5:05
1  
+1 for link to Kenda's poster and sensible advice to set isolation level rather than use hints on every statement –  Paul White Jan 16 '12 at 11:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.