7

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.

Oleg Zhylin
  • 1,290
  • 12
  • 18
  • I sure have such a habit, thanks for the reminder. Remus has provided quite a valuable input though I still hope to get an insight why the phenomenon I observe happens. – Oleg Zhylin Mar 16 '10 at 10:55

2 Answers2

5

First of all, you need to run the query repeatedly under each isolation level and average the result, discarding the one with the maximum time. This will eliminate the buffer warm up impact: you want all runs to be on a warm cache, not have one query warm the cache and pay the penalty in comparison.

Next, you need to make sure you measure under realistic concurrency scenario. IF you will have updates/inserts/deletes occur under real life, then you must add them to your test, since they will impact tremendously the reads under various isolation level. The last thing you want is to conclude 'serializable reads are fastest, lets use them everywhere' and then watch the system melt down in production because everything is serialized.

Other than that, the only isolation level that is legitimately faster is dirty reads, since it doesn't acquire locks. Read committed snapshot (which you did not measure) also doesn't acquire locks, but it does impact performance overall due to row versioning overhead.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Please find updates to my question to address some of the points in your response. By "dirty reads" you mean READUNCOMMITTED, right? – Oleg Zhylin Mar 15 '10 at 22:48
  • 1) Running the query on a cold cache is not accurate. Your production queries will not run on a cold cache, you'll be optimizing an unrealistic scenario and you don't measure the query, you are really measuring the disk read throughput. You need to measure the performance on a warm cache as well, and keep track of both (cold run time, warm run times). 2) dirty reads is read uncommitted. – Remus Rusanu Mar 15 '10 at 23:55
  • How relevant is the cache for a large query (millions of rows) that under normal circumstances runs only once for particular data? – Oleg Zhylin Mar 16 '10 at 00:08
  • Still very relevant. Even if the data is so large that it never fits in memory and each run has to re-read every page of the table, there is still the caching of non-leaf pages (ie. hot pages in the table, root or near root), cache of narrower non-clustered indexes, cache of table metadata. Don't think at your table as an ISAM file. – Remus Rusanu Mar 16 '10 at 00:14
  • I could not grasp - are not ISAM files also cached in RAM? – Gennady Vanin Геннадий Ванин Oct 19 '10 at 06:26
0

Now that I understand isolation levels better I see that better isolation level could allow some smart optimizations. For example, once transaction reads some data isolation level might stipulate it is ought to use that data till the end rather than try to re-read them from disk.

I still would be interested to read some in-depth look on this.

Oleg Zhylin
  • 1,290
  • 12
  • 18