What should you know more about NOLOCK?

As you may know already, NOLOCK is used in some queries to avoid, SQL Server to acquire shared locks on the records being red. The main reason to use nolock is to eliminate the performance overhead, especially for large data sets, due to shared locks. Though you have the gain in performance wise, it also gives you dirty records (uncommitted data).

This article explains another behavior of nolock.

Let’s create a sample table with test data to illustrate the behavior.

use tempdb

go

create table dbo.TestNoLock

(

     col1 uniqueidentifier not null default(newid())

    

)

create unique clustered index uci_col1_TestNoLock on dbo.TestNoLock(col1);

go

I use “uniqueidentifier” date type above to generate different range of values which would create the fragmented table.

I use the below query to populate the table.

while 1=1

insert dbo.TestNoLock default values

stop the execution of the query after 5 seconds.

Check the fragmentation of the table.

select avg_fragmentation_in_percent from sys.dm_db_index_physical_stats

(

DB_ID('tempdb')

,object_id('dbo.T1')

,1

,NULL

,NULL

)

You can notice the table has high fragmentation. More than 99%. 

Check how many pages created for the table.

dbcc ind('tempdb','dbo.TestNoLock',1)

Now execute below two statements and see the order of GUID values.

select * from dbo.TestNoLock

select * from dbo.TestNoLock (nolock)

Look at the last record of both the results. Though at the beginning, order is correct when coming down of the result set, order has changed. However, the execution plans of these two queries remain the same.

When you run two select statements multiple times, the order of each result set is identical, meaning the data order is not changing with the no. of executions.

Let’s reorganize the clustered index and do the rest again.

ALTER INDEX uci_col1_TestNoLock ON dbo.TestNoLock

REORGANIZE ;

GO

Check the fragmentation after reorganization.Fragmentation is little above 2%.

Execute two select statements.

select * from dbo.TestNoLock

select * from dbo.TestNoLock (nolock)

See the result below.

Now you can see the order of both results are same.

Conclusion

Due to the high fragmentation, data access mechanism is not according to the clustered index order when use of NOLOCK or even may be under “read uncommitted” isolation level. (I’ve not tested this scenario with “read uncommitted” isolation level but I assume it would be the same) You need to have explicit ORDER BY clause if you need the data to be ordered according to clustered index with NOLOCK hint.