How to Use NOLOCK and ISOLATION LEVEL to Optimize T-SQL Query Performace

In this article, we will discuss how a NOLOCK hint can help improve performance of queries. Suppose you have a table:

CREATE TABLE Users
(
	UserId INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	FirstName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
	Age INT NOT NULL
)

You would like to select all users between the ages of 20 and 30 so you write a query:

SELECT *
	FROM Users
	WHERE Age > 20 AND Age < 30

When you run this query, SQL Server will put a lock on the table and any other query that will try to access the table will have to wait for that lock to be released, and this is fine if your table is small. Things get slow if your table is big – let's say the Users table has a billion records: any other query will have to wait until the server finishes going row-by-row selecting records where the Age column satisfies the condition.

A way to get around that is to add a NOLOCK hint to the query, which will override locking of the whole table and allow access to it to other queries:

SELECT *
	FROM Users WITH(NOLOCK)
	WHERE Age > 20 AND Age < 30

The WITH keyword is optional – however, if you are selecting from a database on a linked server, you will need to specify it. As far as performance goes, this will not hold off your other queries, but be aware that the query above on a very large amount of data will still be slow – adding an index on the Age column will speed things up significantly:

CREATE NONCLUSTERED INDEX IX_Users_Age ON Users
(
	Age ASC
)

Another thing to note is that you have to be careful with using NOLOCK because it will allow for "dirty" reads. Let's say you execute a query with NOLOCK and the query runs for 30 seconds. Because the table is not locked, during these 30 seconds other queries may have added new rows that the query will not return or modified or deleted rows that were already read. It may also have read data from other queries that were uncommitted and could have been rolled back. So keep that in mind.

If you have a stored procedure that performs multiple SELECT statements which you need to be executed with NOLOCK, you can skip adding WITH(NOLOCK) statements and use ISOLATION LEVEL in the scope of the stored procedure. The NOLOCK hint is equivalent to READ UNCOMMITTED isolation level for the scope of a single query. Let's look at an example – a number of users between ages 20 and 30 who submitted at least one picture in the last month:

CREATE PROCEDURE sp_GetNumberOfUsers20To30WhoSubmittedPictureInLastMonth
	@NumberOfUsers INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @NumberOfUsers = COUNT(u.UserId)
		FROM Users u WITH(NOLOCK)
		WHERE 
			u.Age > 20 AND u.Age < 30
			AND EXISTS(
				SELECT p.PictureId
					FROM Pictures p WITH(NOLOCK)
					WHERE 
						p.UserId = u.UserId
						AND p.SubmittedDate > DATEADD(MM, -1, GETDATE())
			)
END

Note that we have two NOLOCK hints here, one on the Users table and the other one in the subquery on the Pictures table. We can rewrite this stored procedure as follows:

CREATE PROCEDURE sp_GetNumberOfUsers20To30WhoSubmittedPictureInLastMonth
	@NumberOfUsers INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	SELECT @NumberOfUsers = COUNT(u.UserId)
		FROM Users u
		WHERE 
			u.Age > 20 AND u.Age < 30
			AND EXISTS(
				SELECT p.PictureId
					FROM Pictures p
					WHERE 
						p.UserId = u.UserId
						AND p.SubmittedDate > DATEADD(MM, -1, GETDATE())
			)
END

Note that now the WITH(NOLOCK) statements are not needed because we added the ISOLATION LEVEL clause in the beginning of the stored procedure. The scope of the clause is this stored procedure only and any other stored procedures called from within it. Once the stored procedure returns to its caller, the isolation level will be that of the caller.

Remember to be careful when and how you use these as it may produce unexpected results!

This entry was posted in MSSQL and tagged , , , , , , , , , , , , . Bookmark the permalink.

One Response to How to Use NOLOCK and ISOLATION LEVEL to Optimize T-SQL Query Performace

  1. Hi,

    As you correctly stated, the results of using NOLOCK or READ UNCOMMITTED can be inaccurate, but just one other thing on the article. Running a select statement on a table will place a shared lock on the table, and will allow all other read access with this lock in place. It is only when an exclusive lock is issued by an insert/delete, or an update intent lock is upgraded to an exclusive lock, will you start to get query blocking on the data.

    Just might be worth clarifying in the article so the scenarios you might use these hints for are a bit more refined.

    Thanks,

    Stephen

Leave a Reply

Your email address will not be published. Required fields are marked *