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

Related posts about sql-server

Related posts about query