Why better isolation level means better performance in SQL Server
- by Oleg Zhylin
When measuring performance on my query I came up with a dependency between isolation level and elapsed time that was surprising to me
READUNCOMMITTED - 409024
READCOMMITTED - 368021
REPEATABLEREAD - 358019
SERIALIZABLE - 348019
Left column is table hint, and the right column is elapsed time in microseconds (sys.dm_exec_query_stats.total_elapsed_time). Why better isolation level gives better performance? This is a development machine and no concurrency whatsoever happens. I would expect READUNCOMMITTED to be the fasted due to less locking overhead.
Update: I did measure this with
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
issued and Profiler confirms there're no cache hits happening.
Update2: The query in question is an OLAP one and we need to run it as fast as possible. Closing the production server from outside world to get the computation done is not out of question if this gives performance benefits.
