Could someone explain if using with (NOLOCK) in SQL queries actually cause them to run faster? And if so, why?
Asked
Active
Viewed 609 times
1
-
1Have you read the documentation? – DavidG Oct 01 '14 at 08:34
-
1You may wish to read this prior to popping these into your codebase, particularly if you are not sure what they do: http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx – Paddy Oct 01 '14 at 08:37
-
1multiple duplicates are available for same question, you should have referred them before posting duplicate. – Nachiket Kate Oct 01 '14 at 08:37
2 Answers
5
Using NOLOCK doesn't speed up your individual query per se. But it allows for other queries to access the same table therefore reducing time lost through deadlocks.
This is particularly useful when one client has chosen to update a table and takes a significant time to complete the transaction. Using a NOLOCK command, other clients can execute queries that are trying to access the table. They do not have to wait for the update to complete and so, other transactions can complete even while one transaction is still in progress.
This does not imply that the performance of a single query is enhanced by using the NOLOCK command. It simply improves the overall performance where multiple clients are involved or a batch execution is taking place.
However, you need to be aware of the risk of dirty reads when using NOLOCK. Here's a good article explaining why you should generally avoid using NOLOCK.
CodeNewbie
- 2,003
- 16
- 29
0
WITH (NOLOCK) (source):
Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
