Best situation to use READ UNCOMMITTED isolation level

I use READ_UNCOMMITTED (or NOLOCK) when querying production databases from SSMS but not routinely from application code. This practice (along with a MAXDOP 1 query hint) helps ensure casual queries for data analysis and troubleshooting don't impact the production workload, with the understanding the results might not be correct.

Sadly, I see READ_UNCOMMITTED/NOLOCK used widely in production code to avoid blocking at the expense of data integrity. The proper solution is a row-versioning isolation level (SNAPSHOT or READ_COMMITTED with the READ_COMMITTED_SNAPSHOT database option ON) and/or attention to query and index tuning.

I recently code reviewed a proc where the only change was to remove NOLOCK because it sometimes returned wrong results. Removing NOLOCK was a good thing but, knowing that missed or duplicated rows typically happens during allocation order scans of large tables, I suggested also refactoring to use a UNION ALL technique to promote efficient index use. The query now runs in a few milliseconds with correct results, the best of all worlds.

I think it's fine in some circumstances, as long as you accept the consequences, and don't have other options.

For other options, I'd push people towards using Read Committed Snapshot Isolation (RCSI) for new applications, or SNAPSHOT ISOLATION (SI) for older applications, where you can't easily test the entire code base for race conditions with RCSI.

However, those might not be a good fit. You may need to spend some extra time loving and caring for tempdb, and making sure no one leaves an open transaction that makes the version store (and tempdb) grow and fill up the disk.

If you don't have a DBA, or someone whose job it is to monitor and manage your SQL Server, those options can be perilous. More generally, not everyone has full control of the code going to their server where they can change the connection string or code to ask for SI for problem queries.

Besides that, most people don't have locking problems with their entire application. They have problems with stuff like reporting on OLTP data. If you can accept the trade-offs of NOLOCK/RU in exchange for those reports not being blocked by writers, go for it.

Just make sure you understand what that means. It doesn't mean your query doesn't take any locks, it means it doesn't respect the locks taken out by other queries.

And of course, if your problem is writer/writer locking, the only option that'll help is SI, but it would taken an incredible amount of developer work to properly implement that with error handling, etc.

IMHO the only valid use case for READ UNCOMMITTED on a modern system is when debugging one session from another in development, so you could see what it is doing while e.g. a stored proc is still running. It would never normally be used in a production system. There may be some minor performance gain but over the long term it will never be worth it.