EDITOR’S NOTE: Since we last visited the subject of using NOLOCK to make database queries go faster, many other developers and DBAs have weighed in on the subject, adding warnings on its misuse. So the question now is, should you use a WITH (NOLOCK) on tables in your SELECT query? Also, additional SQL Server 2016 resources have been added to this blog.
Eric Cobb, writing in his SQL Nuggets blog, said always doing this is a bad practice. He explained adding NOLOCK to a query “is the same as setting the Transaction Isolation Level to READ UNCOMMITTED.”
Then when would you use NOLOCK? According to Cobb, you might want to use it to help avoid deadlocks with other queries running against the same data, as related in our original blog post. If you can deal with some inconsistencies in the results your query returns, such as long-term sales trends that don’t need real-time data, it might be of use. Or perhaps you are just testing a query and don’t want to tie up the database.
But that points to the problem with NOLOCK – if you use it you can’t guarantee accurate and precise results will be returned consistently from your query. And if you need it to avoid a blocking problem on your query, you’d better go fix the blocking problem instead.
Want to learn more about the worrisome effects of using NOLOCK? Take a look at this list that Aaron Bertrand shared:
- Dirty read
- Missing rows
- Reading rows twice
- Reading multiple versions of the same row
- Index corruption
- Read error
What Other Options Exist?
Reading through Bertrand’s post, you’ll see a nod to Kendra Little’s post on implementing Snapshot or Read Committed Snapshot Isolation. When database load increases get DBAs battling with blocking and deadlocks, solutions are sought. “People start using the NOLOCK hint to work around problems, often with disastrous results,” Little said.
Microsoft plans to remove the NOLOCK hint from SQL Server sometime in the future though that has yet to be put on an exact schedule. What other changes are to come are best followed at the Microsoft Developer Network website.
Come see what Izenda’s embedded self-service business intelligence and analytics has to offer your business.
Other SQL Server Resources
Original Post: 2010/11/02
Every now and then you find a simple way to make everything much faster. We often find customers creating data warehouses or OLAP cubes even though they have a relatively small amount of data (a few gigs) compared to their server memory.
If you have more server memory than the size of your database or working set, nearly any aggregate query should run in a second or less. In some situations there may be high traffic from transactional applications and SQL Server may wait for several other queries to run before giving you your results.
The purpose of this is to make sure you don’t get two versions of the truth. In an ATM system, you want to give the bank balance after the withdrawal, not before, or you may get a very unhappy customer. So by default databases are rightly very conservative about this kind of thing.
Unfortunately, this split-second precision comes at a cost. The performance of the query may not be acceptable by today’s standards because the database has to maintain locks on the server. Fortunately, SQL Server gives you a simple way to ask for the current version of the data without the pending transactions. To better facilitate reporting, you can create a view that includes these directives:
CREATE VIEW CategoriesAndProducts AS SELECT * FROM dbo.Categories WITH(NOLOCK) INNER JOIN dbo.Products WITH(NOLOCK) ON dbo.Categories.CategoryID = dbo.Products.CategoryID
In some cases, queries that are taking minutes end up taking seconds. Much easier than moving the data to a separate database and it’s still pretty much real time, give or take a few milliseconds. You’ve been warned not to use this for bank balances, though.