2

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.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
Jiri Necas
  • 33
  • 6
  • Try them both and see. In theory there should be an improvement because the lock book-keeping is not needed. – Ben Aug 29 '17 at 12:33
  • Short answer: maybe, probably not. Right answer: test. The only way `READ UNCOMMITTED` gains you something here is if 1) there are lots of writes going on and 2) your reads take a long time. In this case, `UNCOMMITTED` will allow snapshot rows to be cleaned up faster since they're not using the old versions of rows. In practice, it would be hard to get a performance gain out of this. – Jeroen Mostert Aug 29 '17 at 12:34
  • Most of the row-versioning overhead is already incurred with the `READ_COMMITTED_SNAPSHOT` database option on. There could be a slight improvement with NOLOCK because the version store would not be accessed but I have not tested performance. I avoid nolock to avoid rows from being skipped or duplicated. – Dan Guzman Aug 29 '17 at 12:35
  • The point Dan makes is significant: `NOLOCK` (or `UNCOMMITTED`, same thing) doesn't just give you dirty reads, it can give you results that are flat-out wrong for any point in time, especially when the database is under heavy load. This makes the (probably tiny) performance gain you can get with it on `SNAPSHOT` databases of dubious value. I would definitely not recommend doing it "because it couldn't hurt and might be faster", because it *can* hurt and probably *isn't* faster. – Jeroen Mostert Aug 29 '17 at 12:47
  • NOLOCK is NOT a performance tool. It is a query hint that has significant impact on the accuracy of your queries. It can in some situations increase the performance but that is a side effect of what is really happening. And most people think dirty reads is only uncommitted transactions from queries, but there are lots of other transactions happening when data shifts. You can and will get missing and/or duplicate rows in addition to many other "fun" issues. You can read more about it here. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ – Sean Lange Aug 29 '17 at 13:18

3 Answers3

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.

Ben
  • 34,935
  • 6
  • 74
  • 113
1

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).

Jiri Necas
  • 33
  • 6
-1

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.

Robert
  • 1