When is it appropriate to use NOLOCK?
I am having timeout issues and deadlocks from time to time with some long running queries.
I'm wondering when is it most appropriate to use NOLOCK and where?
Do I use it on the updates & inserts? or reads?
Answer
Note that you can specify nolock on a per table basis.
I typically used nolock in complex SELECT queries, but only for the little lookup tables that almost never changed, and for display-only data. You know the tables that list the prices for the current half year, or lookups of ids to strings etc. Stuff that only changes with major updates after which the servers are usually restarted routinely anyway.
This improved performance significantly, reduced the chance of deadlock in the busiest times, and more importantly it was really noticable during the worst case moments for queries that touched a lot of tables (which is logical, they have to obtain less locks, and those sidetables are often used nearly everywhere, often decreasing from 7-8 to 4 tables that need to be locked)
But be very careful adding it, don't rush it, and don't do it routinely. It won't hurt when used properly, but it will hurt horribly when used improperly.
Don't use it for highly critical stuff, stuff that calculates etc, because it will get inconsistent, anything that leads to a write sooner or later.
Another such optimization is ROWLOCK, which only locks on row level. This is mainly useful when updating (or deleting in) tables where the rows are not related to eachother, like tables where you only put in log records (and the order in which they are inserted doesn't matter). If you have a scheme that somewhere in the end of an transaction a log record is written to some table, this can speed up considerably too.
If your database has a relatively low percentage writes it might not be worth it. I had a read:write ratio of under 2:1.
Some URLs I saved when working on this:
http://www.developerfusion.com/article/1688/sql-server-locks/4/