Does With(NoLock) help with query performance?

Question

Most of the select statements in our stored procedures use WITH(NOLOCK) and I was told by my co-workers that they use it to improve performance. Now, I know that this hint helps with locking but does it actually improve performance? I've read somewhere that we shouldn't use the NOLOCK hints in OLTP databases but I see the opposite being done at my workplace. Could someone shed light on the best practices when it comes to using the NOLOCK hints?

Answer 1

uncommitted

The NOLOCK hint is a synonym of the READ UNCOMMITTED isolation level. Please consider them to be equivalent and interchangeable for the duration of this answer. Read Uncommitted is implemented in SQL Server to take minimal schema locks that prevent alterations to the table(s) being queried.

• Adding or dropping indexes
• Adding or dropping columns
• Adding or dropping constraints
• Some index rebuild and reorganization commands

Using either will still take schema locks, but they will be allowed to ignore locks taken by modification queries (insert, update, delete, merge) that have not yet completed their task(s) and either rolled back or committed the transaction(s).

All read queries outside of those that use exclusive locking hints (e.g. UPDLOCK) inside of a transaction will take and quickly release locks at different granularities (row, page, etc.) as chosen by the storage engine. There is no lock escalation for read queries.

The difference is in how isolation levels behave when they encounter locked rows, pages, or objects.

Under Read Committed, Repeatable Read, and Serializable, the read query is blocked until the modification(s) release their locks.

Under Read Committed Snapshot Isolation and Snapshot Isolation, the read query will read the last known good version of locked rows from the version store.

Much of the "my query got faster with NOLOCK" conclusions come (incorrectly) from avoiding being blocked. Under Read Committed, read queries can also block and deadlock with modification queries under some circumstances. See:

• When Read Queries Block Write Queries In SQL Server
• Why Read Queries Deadlock With Write Queries In SQL Server

Using either NOLOCK as a query hint or Read Uncommitted as an isolation level will avoid these scenarios. Again, they're the same thing.

There is an additional consideration of course, in that queries that specify certain locking hints can use IAM scans to read fewer pages.

Note the ~6 second difference at the clustered index scan for both the query that used NOLOCK and the query that used TABLOCK as hints.

usage

Since you asked:

• If you care about data correctness, don't use NOLOCK as a query hint or READ UNCOMMITTED as your isolation level.
• If your customers might get angry about incorrect results, don't use NOLOCK as a query hint or READ UNCOMMITTED as your isolation level.
• If bad decisions might get made either by your application or by consumers of the data, don't use NOLOCK as a query hint or READ UNCOMMITTED as your isolation level.

From The Read Uncommitted Isolation Level:

To avoid the most annoying blocking and deadlocking scenarios, and return correct data as a result, the Read Committed Snapshot Isolation level or Snapshot Isolation level is your best bet.