Wednesday, July 20, 2016

much ado about NOLOCK

In most cases it does not cause problems and in some cases may improve performance of the query.

If the query includes tables that are extremely volatile (data changing very rapidly) then there is a chance of a “dirty read” where an older version of the record is read while that record is currently being in process of being updated by another transaction.

Contrary to the name of the query hint (NOLOCK) it does not completely eliminate locking caused by the query, but it does reduce it.

I don’t recommend putting it arbitrarily on every query by default -- something I’m guilty of myself :)  

Instead recommend using it when other optimization strategies are not practical, the risk of dirty reads is acceptable, and it actually makes a difference in the speed the query returns results.

https://www.brentozar.com/archive/2011/11/theres-something-about-nolock-webcast-video/

https://www.brentozar.com/archive/2013/02/7-things-developers-should-know-about-sql-server/

https://www.brentozar.com/archive/2015/03/top-3-mistakes-t-sql-developers-make/