Quick Tips for Faster SQL Servers: When (Not) to Use NOLOCK

Many developers mistakenly use WITH (NOLOCK) in their SELECT statements without fully understanding its effects. While they might aim to speed up queries or prevent deadlocking, NOLOCK can actually lead to invalid results.

What does NOLOCK do?

A SELECT statement in SQL Server locks data to prevent modifications during querying. The lock is released after the query finishes, allowing data changes to resume. These locks ensure SELECT statements return only valid data, not data that’s in the process of being modified.

Using NOLOCK in your query removes data protection safeguards. It’s the same as setting the Transaction Isolation Level to READ UNCOMMITTED, which allows the query to pull in data that is in the process of being modified and has not been committed to the database yet. In SQL Server terms, this is called a “Dirty Read” and should not be trusted. With a Dirty Read, the data returned by your query may or may not exist, depending on the outcome of other T-SQL statements that may be running at the same time.

In most cases within a transactional (OLTP) database, the results from a SELECT statement using NOLOCK should not be relied upon for accuracy. However, there are occasional exceptions (details below).

Why Does NOLOCK Make My Query Faster?

Too often, NOLOCK is used as the “turbo-boost” button for your queries. While it’s true that a SELECT query will run faster when using NOLOCK, it does so by ignoring the safeguards put in place to ensure that your query is returning accurate data. By not putting these locks in place, you reduce the overhead of the query and bypass part of the process SQL Server normally uses when processing your query, thereby making it run faster.

However, some of the side effects of using NOLOCK include missing rows, reading rows twice, reading multiple versions of the same row, and returning rows that have been deleted.

What’s the point in having a faster query if it doesn’t return the correct results?

When should I use NOLOCK?

NOLOCK can be useful when querying data that doesn’t change often, or in the case of an analytical (OLAP) database where data writing only occurs during a scheduled time, like a data warehouse that only runs INSERT/UPDATE/DELETE statements at night and where users do reporting during the day.

For example, think about a “States” table that just contains a list of US states. The list isn’t going to change, and you won’t have to worry about that data being modified while you’re querying it. In a case like this, using NOLOCK shouldn’t cause any issues.

Alternatively, there are situations where your query can accept certain inconsistencies. For instance, when querying sales trends over the last 6 months, real-time precision may not be necessary.

The main advantage of using NOLOCK is that you can avoid deadlocks with concurrent queries. However, frequent deadlocks may indicate that using NOLOCK will return incorrect results.

Avoid NOLOCK to Maintain Data Consistency and Integrity

You should rarely, if ever, use NOLOCK in a transactional database. You certainly shouldn’t be using it blindly in all of your SELECT queries. If you can tolerate some data inaccuracy in your results, then NOLOCK may be OK. Overall, I’d still recommend prioritizing data integrity by steering clear of NOLOCK.

Want to work with The SERO Group?

Want to learn how SERO Group simplifies SQL Server management? Schedule a no-obligation discovery call to get started.