Many times we face performance problems specially when we try to query data from tables with thousands or millions of records.
There are many things you can do to improve Query performance (Indexes, Avoid Cursors, have normalized data, etc) but in this post I will only talk about the use of NOLOCK.
So why NOLOCK?
NOLOCK is a nice feature that used in the right situations could improve your query performance significantly, with very little effort.
How it works?
Simply put, MSSQL locks records every time is running a SQL transaction, this could be a simple UPDATE query or an entire background process from a stored procedure, trigger or even a website manipulating data.
By default when you query a table, it waits the exact time until there is nothing locking the data to pull the records to ensure data Integrity (when all the changes have been committed).
But what if you just want to pull the data regardless if the changes are up to date?
Let me use a better example.
A live website with thousands of users, will always be changing the data and running stored procedures manipulating the data in all ways.
I’ve been asked for a sales report from the month of June (last month), so I know that since is a past month, no matter what the user change, the sales from june will not change.
So I just add NOLOCK hint to my query report, in order to pull it faster.
Here is how to use it:
SELECT * FROM PERSON.Person WITH (NOLOCK)
Use with joins:
SELECT * FROM SALES.SalesOrderHeader SOH WITH (NOLOCK) INNER JOIN SALES.SalesOrderDetail SOD WITH (NOLOCK) ON SOH.SalesOrderID = SOD.SalesOrderID