You might have already know that WITH NOLOCK table hint will allow the query to do a dirty fetch and reads uncommitted records from the table. It is just the same as using READUNCOMMITTED. You may think that fetching uncommitted records from the table is a great problem. But there are many situations where fetching uncommitted data is not as worse as creating a deadlock.
So let us see how NoLock hint increase the performance of the query. Consider you are updating multiple records within a transaction. A normal sql select query will wait for the transaction to complete, thus degrading the performance of the application or screen which is executing the select query.
To experiment this, open a query window (Window-A) in SQL Server Management Studio. In Window-A begin a transaction and then execute an insert statement leaving the transaction open. i.e. You are not going to run the COMMIT TRAN statement now, just like as seen in the below screen shot leave the query window without closing the transaction.
Open another query window (say Window-B). In Window-B, execute a select query against the same table. You will notice that select query will run for ever as seen in the below screen shot.
select * from MyTecBits_Names_1 where name like '%53%'
Go back to Window-A and run the COMMIT TRAN statement. Check the Window-B, the statement will be now completed fetching the records.
Now, Add the with nolock to the query like below and do the same experiment. You will notice the select query will have completed the execution and the table data will be listed in the result section
select * from MyTecBits_Names_1 WITH (NOLOCK) where name like '%53%'
This clearly shows the importance of With (NoLock) table hint in performance improvement. Of course there will be trade-off. You have to decide how important is to avoid dirty fetch over performance.
Related Articles
Do you want to test how Indexing a table column improve performance? Read the article Does Index Improve SQL Server Query Performance?
Thanks for the post. if you would have added a sample on how to use nolock in the end of your explanation it would add more value to your post.