Should I use WITH (NOLOCK) to make SQL Server queries faster?

Open almost any SQL Server codebase and you'll find it on every table:

SELECT o.OrderID, c.Name
FROM dbo.Orders AS o WITH (NOLOCK)
JOIN dbo.Customers AS c WITH (NOLOCK) ON c.CustomerID = o.CustomerID;

Someone added it years ago because "it makes queries faster and stops blocking". Should you use it? Almost never, and there's a better fix for the problem it was meant to solve.

What NOLOCK actually does

WITH (NOLOCK) is the same as READ UNCOMMITTED for that table. The query doesn't take shared locks while reading and ignores other sessions' exclusive locks. That's why it doesn't wait on blocked rows.

People usually know it can return dirty reads: data from transactions that later roll back. That's the smallest problem. The others are worse:

1. Missing rows. A NOLOCK scan can read pages in allocation order. If another session splits a page or moves rows during the scan, rows can move behind the scan's position and never be read.

2. Duplicate rows. The same movement can put a row ahead of the scan, so it's read twice. Your SUM of order totals can be larger than the real total.

3. Inconsistent rows. A row can be read halfway through an update to a large or off-row value.

4. Errors. Under heavy concurrent change you can get:

Msg 601: Could not continue scan with NOLOCK due to data movement.

None of this shows up in testing. It happens under production concurrency, in the reports people trust the most.

It isn't lock-free either

NOLOCK queries still take schema stability (Sch-S) locks. They're blocked by, and they block, schema changes, index rebuilds and partition switches. "Never blocks" isn't true.

Why it seems to work

It mostly returns correct data because most rows aren't changing at any given moment. It does remove blocking between readers and writers. But the underlying problem, readers and writers waiting on each other, has a fix that doesn't return wrong answers.

The better fix: row versioning (RCSI)

Read Committed Snapshot Isolation makes every READ COMMITTED read see the last committed version of each row, taken from a version store, instead of waiting on writers' locks:

ALTER DATABASE YourDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

What you get:

• Readers don't block writers, and writers don't block readers, which is exactly what NOLOCK was for.
• Each statement sees consistent committed data: no dirty reads, no skipped or duplicated rows.
• No code changes. Every existing query benefits automatically, and you can remove the NOLOCK hints over time.

It's the default behavior in Azure SQL Database and how PostgreSQL and Oracle work out of the box.

Costs to plan for:

• tempdb load: row versions are stored in tempdb (or in the database itself when Accelerated Database Recovery is on in SQL Server 2019+). Monitor version store size, especially with long-running transactions.
• 14 bytes per row of versioning overhead on modified rows.
• Behavior change for "read then write" logic. Code that relied on blocking to serialize, such as reading a balance and then updating it, may need UPDLOCK or an atomic UPDATE instead. Review queue-style tables and counters before you switch.

The ALTER DATABASE needs a moment with no other active connections. WITH ROLLBACK IMMEDIATE forces that, so run it in a maintenance window.

If you need a transaction-wide consistent view (a multi-statement report), enable ALLOW_SNAPSHOT_ISOLATION and use SET TRANSACTION ISOLATION LEVEL SNAPSHOT for those sessions.

When NOLOCK is acceptable

• Ad-hoc DBA queries on a busy production box, such as "roughly how many rows are in this table?", where approximate is fine.
• Monitoring dashboards that tolerate occasional wrong numbers.

Never use it for money, inventory, anything that feeds a decision, or ETL that copies data elsewhere.

Also fix the blocking itself

If reads are blocked a lot, look at the writers too: long transactions, missing indexes that make updates scan and lock whole ranges, and lock escalation from huge batch updates. RCSI removes read/write blocking, but writers still block other writers.

Checklist

1. NOLOCK means READ UNCOMMITTED: dirty reads, missing rows, duplicate rows and error 601.
2. It still takes Sch-S locks, so it isn't lock-free.
3. Enable RCSI to stop readers and writers blocking each other with consistent results.
4. Keep NOLOCK for throwaway, approximate queries only.