sqlsql-serverperformancenolock

SQL server with (NOLOCK) hint


Could someone explain if using with (NOLOCK) in SQL queries actually cause them to run faster? And if so, why?


Solution

  • 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.