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

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

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

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.
{"cc_re__cc_re__cc_by_sa_3_0": ["                  <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2015 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2015.3.4.2355\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Database Administrators Stack Exchange works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class="], "copyright__copyright__copyright": ["\"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.\n\nOnly by asking them you can have a clear picture.\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}