Phil Singer wrote:
ryan_gaffuri@xxxxxxxxxxx wrote:I met a SQL Server DBA who told me that SQL Server 2005 has something akin to multi-versioning. Does anyone know anything about this? My understanding is that reads no longer block writes. is this true?I would love to read a discussion of this by someone who really understands Oracle and SQL Server 2005. What I have read is strictly from the MSFT camp, and it is obvious that whoever wrote it did not understand Oracle.What little I know: SQL Server 2005 has something analogous to Oracle's UNDO. It is not turned on by default. It works in a similar manner (but the devil is in the details!). Using it appears to exact some performance penalties.
Speak of the devil, and he shall appear. Here are some of the (painful) details.
SS2k5 introduces multiversioning by adding two new isolation levels - snapshot isolation and read-committed snapshot isolation. The idea is similar to Oracle's, but the implementation is a hack job in my opinion. Oracle enables its multiversioning by using undo information to be used to reconstruct blocks *if they're required* to present an scn-consistent view of something to a reader (yeah, I'm preaching to the choir here, I know, but the key is Oracle is storing what's needed to reconstruct them if needed, not blindly copying whole rows for no good reason).
SS2k5 is versioning the whole row by storing previous copies in its tempdb, and in the case of triggers, trawling the transaction log to re-materialise the old rows in tempdb. Big problem ... tempdb has enough contention as it is usually, as all sorts, hashes, server-side cursors, etc. already compete for love and attention there. This is akin to combining undo and temporary segments into one storage bucket in Oracle, with all the joy of conflicting workloads ... yuk! Oh, and did I mention the whole row thing? Change your 4-byte char, and your whole varchar(4000) in the same row that you didn't touch is stored away too ... gotta have that whole row! Oracle *may* do this when needed, but my understanding was it's way smarter than that for fixed-length changes to rows/blocks, etc. Microsoft claims the version store (the bit of tempdb used for this) holds old rows only as long as needed, but doesn't detail the algorithm or mechanics, so nothing like undo retention periods, etc.
The contention on tempdb, and the unnecessary store-the-whole-row-without-thinking behaviour are the two big impacts on performance.
Because the feature is presented as an isolation level (rather than as immutable server-wide behaviour), different client connections can have different settings! The behaviour when some clients expect (and need) multiversioning run into clients using other traditional isolation levels is unpredictable ... there have been a few WTF moments already on some mailing lists. Microsoft's solution to this is "ensure all clients use appropriate isolation levels" ... kinda hard when any client can issue a set isolation level command at a whim.
Bah ... that's enough ranting from me. Sounds like a good blog article topic, so I'll go and write more detail about it there.
Ciao Fuzzy :-) -- http://www.freelists.org/webpage/oracle-l