Why better isolation level means better performance in SQL Server
Posted
by Oleg Zhylin
on Stack Overflow
See other posts from Stack Overflow
or by Oleg Zhylin
Published on 2010-03-15T22:04:58Z
Indexed on
2010/03/16
6:26 UTC
Read the original article
Hit count: 319
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.
© Stack Overflow or respective owner