SQL Server – Read but Not Update Ops Fail Due to Tempdb Space Issues

snapshot-isolationsql servertempdbtempdb-version-storetransaction

MSDN online article "Choosing Row Versioning-based Isolation Levels" states:

"tempdb must have enough disk space for the version store. If there are very long-running transactions, all the versions generated by update transactions during the time must be kept in tempdb. If tempdb runs out of space, update operations do not fail, but read operations using row versioning might fail"

Can you explain me why?
taking into account that (quoting the same article):

"When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, update and delete transactions for a particular database must maintain row versions even when there are no transactions using a row versioning-based isolation level… Because the record versions are stored in tempdb"

that is, update (but not read) operations require space for row versioning should have IMO failed due to lack of space.
And why do read ops fail if they do not require version storage in tempdb ?

Best Answer

^{(Converting my comment to an answer. I don't know if this is definitive, but it makes sense to me. If you can prove it wrong, let me know in the comments.)}

The row versions aren't actually required to complete a write operation; they're only required by readers that come along and need those versions of the rows. If they need them.

If tempdb runs out of space during version generation, while that's not okay, it may end up working out if no readers need those versions. A read operation would only fail if it needs a version of a row that isn't in tempdb.

Furthermore, any write operation is considered more important than a SELECT, for obvious reasons (same idea as when the Deadlock Monitor figures out which process to kill if there's a deadlock), so it's preferable to fail a SELECT if possible.

Related Solutions

Find Transactions Filling Up the Version Store in SQL Server

It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it?

You can track this by object, though, which can help you narrow down which modules are causing the churn. Have a look at sys.dm_tran_top_version_generators:

USE [your database];
GO
SELECT obj = 
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  vs.aggregated_record_length_in_bytes
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1);

And on SQL Server 2008+, you can also figure out which modules reference these tables by adding sys.dm_sql_referencing_entities:

SELECT 
  obj = QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  referenced_by = QUOTENAME(r.referencing_schema_name)
  + '.' + QUOTENAME(r.referencing_entity_name),
  vs.aggregated_record_length_in_bytes AS size
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
CROSS APPLY sys.dm_sql_referencing_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)), 'OBJECT'
) AS r
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1)
ORDER BY size DESC, referenced_by;

This assumes that none of the version store could be created by ad hoc queries. However, it doesn't tell you which of those modules could be causing it - hopefully the naming scheme is logical and helps you narrow it down a bit.

(On 2005 you might be able to go through sysdepends and other old-style dependency views but I'm not 100% sure how reliable that would be.)

Sql-server – Sql server snapshot isolation overhead when read_committed_snapshot is enabled

The underlying mechanics is pretty much the same. However, in snapshot isolation the versions are potentially kept significantly longer causing a higher strain on the resources overall.

Best Answer

Related Solutions

Find Transactions Filling Up the Version Store in SQL Server

Sql-server – Sql server snapshot isolation overhead when read_committed_snapshot is enabled

Related Question