The finance summary had run every night for two years with WITH (NOLOCK) on every table, added by a long-gone contractor to "avoid blocking." Nobody could point at a problem, so nobody touched it. Then a quarterly reconciliation ran the report twice in one evening and got two different totals — a difference of one invoice, 14,200 euros, present in the first run and absent in the second, with no dirty, uncommitted data anywhere near the table. What the report had hit was worse than a dirty read: an allocation-ordered scan walking the index while a concurrent insert split a page, reading one range of rows before the split and again after it moved. The same rows counted twice. NOLOCK had not been returning slightly-stale correct data for two years; it had been returning data with no consistency contract at all, and the totals had been silently wrong on an unknown number of nights.
This is the article I send when someone proposes NOLOCK as a performance feature: what it actually does to your result set, the locks it still takes despite the name, and the alternatives that solve the real problem. Applies to SQL Server 2016 through 2022.
What does NOLOCK actually allow into your results?
NOLOCK sets the effective isolation for that table read to READ UNCOMMITTED, which permits dirty reads — rows from transactions that may still roll back — but the deeper problem is that it abandons positional consistency, so a scan can return the same row twice or skip committed rows entirely. The mechanism deserves the explanation most NOLOCK discussions skip. When a scan runs under read uncommitted on a table large enough, the engine may drive the scan in allocation order, following the IAM pages rather than the index's linked list, because allocation order is faster and consistency does not constrain it. Now insert a concurrent writer splitting a page ahead of the scan: rows move from a page the scan has passed to a page it has not yet reached, and they get read again. Split a page behind the scan's position and rows move into territory it already covered, and they are never read at all. No uncommitted data is involved in either case — the rows are committed, the totals are wrong, and the report reproduces the bug only when the timing aligns. That is the failure mode from the reconciliation: not a race you can fix with cleaner data, but a result set with no guarantee of corresponding to any moment in time.
Does NOLOCK really take no locks?
No — a NOLOCK read still takes a schema stability lock (Sch-S) on every table it touches, which means it can be blocked by schema changes and, more surprisingly, it can itself block. The name describes what it skips — shared row and page locks, and the lock escalation behavior that comes with them — not what it takes. The practical consequence shows up in maintenance windows: an ALTER TABLE or index operation needs a schema modification lock, and a NOLOCK report holding Sch-S queues that DDL exactly like any other reader would. I have watched a deployment hang for forty minutes behind a "lock-free" report. The scan can also simply fail: when the allocation-ordered scan encounters movement it cannot navigate, SQL Server raises error 601, "Could not continue scan with NOLOCK due to data movement," and the query terminates. A hint sold as making reads unstoppable produces a class of transient, retry-worthy errors that locked reads never see — if your error logs contain 601s, your NOLOCK queries are being stopped by the very data movement they were supposed to glide over.
What is NOLOCK doing to the queries that use it?
Beyond correctness, it changes the shape of reads in ways that hurt. Allocation-ordered scans bypass the index's logical order, so a NOLOCK scan that someone expects to benefit from index ordering may read pages in physical order and then sort afterward — and it can read pages that locked scans would skip via row-level locking navigation. It also trains teams to stop investigating blocking: the hint goes on, the blocking symptom disappears, and the underlying write pattern that caused the contention — the long transactions and hot-row updates from my blocking chain notes — never gets fixed. NOLOCK as a response to blocking is treating the smoke detector. And one scope note that bites ORM users: applying NOLOCK to the target of an UPDATE or DELETE is deprecated and does not relax the locks the write itself takes — writes lock, always, hint or not. The hint only ever applied to the read side, and treating it as a general de-blocker misunderstands it in both directions.
When is NOLOCK genuinely acceptable?
It is acceptable when the consumer provably does not need per-row consistency: approximate operational dashboards, trend sampling, row-count estimates on tables where a moving total is the expected behavior, and read-only or effectively-static data where no writer exists to create movement. A monitoring query sampling sys.dm views with NOLOCK is fine — the DMVs are inconsistent by nature. A warehouse staging table that is loaded, then read while no load runs, is fine. The test I apply is the reconciliation test: if anyone will ever compare this query's output against another source and act on the difference, the query needs a consistency contract, and NOLOCK does not offer one. Finance summaries fail that test. So does anything feeding a downstream write — reading with NOLOCK and writing decisions based on the read manufactures the duplicates and gaps into durable data, which is how a reporting shortcut becomes a data-quality incident.
What should replace NOLOCK for the blocking problem?
Read committed snapshot isolation replaces it for the vast majority of real cases: readers get a statement-consistent snapshot of committed data, writers are never blocked by readers, and the duplicates-and-gaps failure mode does not exist because the snapshot is a real moment in time. Enabling it is one line, and my RCSI and snapshot isolation notes cover the tempdb version store cost that comes with it — the honest price sheet, which is real but bounded and monitorable, unlike NOLOCK's price of unbounded wrongness:
ALTER DATABASE [Orders] SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = N'Orders';RCSI changes the behavior of the default read committed isolation for every query in the database without touching application code — which is precisely the property that makes it the answer to a NOLOCK habit: you remove the hints and the correctness problem and the blocking problem in the same change. For queries that need multi-statement consistency, full snapshot isolation extends the same machinery across a transaction. The workloads I have migrated off NOLOCK to RCSI kept the reader-writer non-blocking they wanted and got back the consistency they did not know they had lost. The migration is mostly deleting hints and watching the version store — a far better trade than discovering, two years in, that the nightly totals were approximate.
Watching isolation behavior with MonPG when SQL Server support lands
The signals that matter here are error 601 counts from the error log, LCK_M_ wait time before and after any RCSI migration so the improvement is measured rather than assumed, and version store size trended as the cost side of the snapshot ledger. A codebase-wide count of NOLOCK hints, taken from the plan cache or the source repository, is also a legitimate health metric — and one that should trend toward zero. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, grep your procedures for NOLOCK, alert on 601, and let the reconciliation test decide which queries get to keep their hints.