Why Better Isolation Means Better SQL Server Performance

Question

When measuring performance on my request, I came across a relationship between the level of isolation and elapsed time, which was unexpected for me.

READUNCOMMITTED - 409024 READCOMMITTED - 368021 REPEATABLEREAD - 358019 SERIALIZABLE - 348019 

The left column is the table hint, and in the right column is the elapsed time in microseconds (sys.dm_exec_query_stats.total_elapsed_time). Why does a better insulation give better performance? This is a development machine, and concurrency does not occur at all. I would expect READUNCOMMITTED to be hunger due to less overhead.

Update: I did it with

 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE 

and Profiler confirms that caching does not occur.

Answer 1

First of all, you need to run the query again at each isolation level and average the result, discarding the one with the maximum time. This will eliminate the effect of buffer warm-up: you want all runs to be in a warm cache, and not just one request heats the cache and pays a fine in comparison.

Then you need to make sure that you are measuring a realistic concurrency scenario. If you get updates / inserts / deletes in real life, then you should add them to your test, as they will greatly affect reading at different isolation levels. The last thing you want is to conclude that serializable reads are the fastest, let you use them everywhere, and then watch how the system melts during production because everything is serialized.

Other than that, only the isolation level, which is legally faster, is dirty because it does not receive locks. A read fixed snapshot (which you did not measure) also does not receive locks, but it affects overall performance due to line overhead.

Answer 2

Now that I have a better understanding of isolation levels, I see that a better level of isolation may allow some smart optimizations. For example, as soon as a transaction reads some level of data isolation, you can indicate that it should use this data to the end, rather than trying to re-read it from disk.

I would still be interested to read a detailed overview of this.