Why better isolation level means better performance in MS 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/15 22:09 UTC
Read the original article Hit count: 392

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.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about query