214

I guess the real question is:

If I don't care about dirty reads, will adding the with (NOLOCK) hint to a SELECT statement affect the performance of:

  1. the current SELECT statement
  2. other transactions against the given table

Example:

Select * 
from aTable with (NOLOCK)
Bob Probst
  • 9,533
  • 8
  • 32
  • 41
  • 4
    These [SO](http://stackoverflow.com/a/12610346/641833) and [DBA](http://dba.stackexchange.com/a/50029/9210) answers are a little clearer as to what is actually happening. – Trisped Jan 21 '16 at 20:55

5 Answers5

312

1) Yes, a select with NOLOCK will complete faster than a normal select.

2) Yes, a select with NOLOCK will allow other queries against the effected table to complete faster than a normal select.

Why would this be?

NOLOCK typically (depending on your DB engine) means give me your data, and I don't care what state it is in, and don't bother holding it still while you read from it. It is all at once faster, less resource-intensive, and very very dangerous.

You should be warned to never do an update from or perform anything system critical, or where absolute correctness is required using data that originated from a NOLOCK read. It is absolutely possible that this data contains rows that were deleted during the query's run or that have been deleted in other sessions that have yet to be finalized. It is possible that this data includes rows that have been partially updated. It is possible that this data contains records that violate foreign key constraints. It is possible that this data excludes rows that have been added to the table but have yet to be committed.

You really have no way to know what the state of the data is.

If you're trying to get things like a Row Count or other summary data where some margin of error is acceptable, then NOLOCK is a good way to boost performance for these queries and avoid having them negatively impact database performance.

Always use the NOLOCK hint with great caution and treat any data it returns suspiciously.

tom.dietrich
  • 8,219
  • 2
  • 39
  • 56
  • Thanks. This is what I've been assuming but was questioned about it by a colleague and my initial research made me question myself. SQLServer 2005 documentation says that with NOLOCK is the default locking scheme for all select statements! I'd guess then that my hints would be redundant in ... – Bob Probst Oct 16 '08 at 20:55
  • 2
    ... 2005 and not have any effect. We're running 2000 right now (thanks to our vendor) and there no similar statement in the documentation. – Bob Probst Oct 16 '08 at 20:57
  • 4
    Your friend needs to read the documentation. Table Hint (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx – Pittsburgh DBA Oct 16 '08 at 21:48
  • 9
    Side note: if this was true, it would be absolute chaos. – Pittsburgh DBA Oct 16 '08 at 21:53
  • 1
    Points 1 and 2 need to be restricted to 1) "... _when an insert/update/delete action is pending on the table_." and 2) "... will allow _insert/update/delete_ queries against ...". I would (personal preference) change instances of "faster" to "sooner" since the difference is waiting for another process complete. – Trisped Jan 21 '16 at 20:40
65

NOLOCK makes most SELECT statements faster, because of the lack of shared locks. Also, the lack of issuance of the locks means that writers will not be impeded by your SELECT.

NOLOCK is functionally equivalent to an isolation level of READ UNCOMMITTED. The main difference is that you can use NOLOCK on some tables but not others, if you choose. If you plan to use NOLOCK on all tables in a complex query, then using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is easier, because you don't have to apply the hint to every table.

Here is information about all of the isolation levels at your disposal, as well as table hints.

SET TRANSACTION ISOLATION LEVEL

Table Hint (Transact-SQL)

Pittsburgh DBA
  • 6,672
  • 2
  • 39
  • 68
  • 3
    I agree, but not completely; it is important to point out that a NO LOCK / READ UNCOMMITTED hint doesn't actually improve the speed of the query, but more-so makes it appear faster because it doesn't have to wait for previous queries to complete. So really, the query SEEMS faster, rather than IS faster (I think). – Möoz Aug 07 '13 at 02:00
  • 1
    @BorhanMooz Well, there is the savings from not having to request locks from the lock manager. Even if there are no other queries waiting, this has a cost and a memory overhead. – Pittsburgh DBA Aug 07 '13 at 19:09
  • 1
    I was discussing this with some of my work-mates. As I understand it, a query using a WITH(NOLOCK) hint still requires a Schema-Shared lock to be attained (http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/). – Möoz Aug 08 '13 at 01:11
  • 1
    Yes, but not thousands of shared locks on pages or extents. A Schema lock is ONE lock, not THOUSANDS. If we want to be pedantic, we can continue to debate this, but yes, there will be a minimal amount of lock overhead. The savings are significant. Do the math on this: http://msdn.microsoft.com/en-us/library/aa337559(v=sql.100).aspx – Pittsburgh DBA Aug 08 '13 at 03:22
14

In addition to what is said above, you should be very aware that nolock actually imposes the risk of you not getting rows that has been committed before your select.

See http://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

7

It will be faster because it doesnt have to wait for locks

StingyJack
  • 19,041
  • 10
  • 63
  • 122
  • 1
    How much faster? Could you provide any speed improvements numbers? – Eugeniu Torica Apr 16 '10 at 11:51
  • 6
    "How much" depends on what you are specifically doing with your data and how long you typically have to wait for a lock acquisition. – StingyJack Apr 19 '10 at 16:40
  • 1
    The only correct benchmark is the one you create & run yourself. What everybody tells you is as good as "the check is in the mail". I've proven many myths and assumptions wrong many times by running my own benchmarks. – TravisO Aug 20 '13 at 19:10
  • 1
    ^ @TravisO - is completely correct. I've had NOLOCK execute slower a few times. Not entirely sure why, but I'm using that when troubleshooting and I dont want to negatively (over)affect production – StingyJack Jun 29 '17 at 19:52
2

  • The answer is Yes if the query is run multiple times at once, because each transaction won't need to wait for the others to complete. However, If the query is run once on its own then the answer is No.

  • Yes. There's a significant probability that careful use of WITH(NOLOCK) will speed up your database overall. It means that other transactions won't have to wait for this SELECT statement to finish, but on the other hand, other transactions will slow down as they're now sharing their processing time with a new transaction.

Be careful to only use WITH (NOLOCK) in SELECT statements on tables that have a clustered index.

WITH(NOLOCK) is often exploited as a magic way to speed up database read transactions.

The result set can contain rows that have not yet been committed, that are often later rolled back.

If WITH(NOLOCK) is applied to a table that has a non-clustered index then row-indexes can be changed by other transactions as the row data is being streamed into the result-table. This means that the result-set can be missing rows or display the same row multiple times.

READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.

WonderWorker
  • 8,539
  • 4
  • 63
  • 74