Do I get any performance gain by using WITH (NOLOCK) on SQL Server database where READ_COMMITTED_SNAPSHOT is switched on?

Question

I have a database on Microsoft SQL Server 2014 where READ_COMMITTED_SNAPSHOT property is switched on. I understand that this means that reads are not blocked by writes because reads do not issue shared locks and that this is clean reading.

My question is: do I get any performance gain by using WITH (NOLOCK) in select statements in this scenario? In my case I don't mind it would be dirty reading.

I tried to find this information but only found the comparison between using WITH (NOLOCK) and switching READ_COMMITTED_SNAPSHOT on. But I already have it on.

Answer 1

In theory there should be an improvement because although the query will not block in either case, in the READ_COMMITTED_SNAPSHOT case the book-keeping associated with the locks is still required, so that the database knows when it needs to create/retain/cleanup snapshotted rows/pages.

However as with all performance questions, you should try it and see if the difference exists in practice, and if it is, if it matters to your use-case.

Answer 2

Thank you all for your suggestions.

We've made some tests and it appears that using WITH (NOLOCK) still makes a difference in performance even when READ_COMMITTED_SNAPSHOT is switched on.

The test case was: heavily updating some table in transaction (update of 3M records) while reading from the same table in another connection. Using or not using WITH (NOLOCK) in this read statement made a huge performance difference (using WITH (NOLOCK) faster).

Answer 3

But watch out in Azure DB! We had NOLOCK's (from on-premise days) and found that in a few circumstances they sent Azure DB berzerk: i.e. queries (with NOLOCK's) which previously ran just fine would, on Azure DB, run very slowly until we removed the NOLOCK's. That's contrary to all advice and all documentation, all the theory, etc. But then Azure DB is an odd beast and theory still frequently doesn't hold in practice.