Optimistic Isolation Levels - Pro SQL Server Internals - page 427

Version Store Behavior
As already mentioned, you need to monitor how optimistic isolation levels affect your system. For example, let's run
the following statement that deletes all rows from Delivery.Orders table, as shown in Listing 21-5.
Listing 21-5. Deleting all orders from the table
set transaction isolation level read uncommitted
begin tran
delete from Delivery.Orders
commit
It is worth mentioning that a session runs in READ UNCOMMITTED mode. Even if there are no other
transactions using optimistic isolation levels, there is still a possibility that they will start before the DELETE transaction
commits. As a result, SQL Server needs to maintain the version store, regardless of whether or not there are any active
transactions that use optimistic isolation levels.
Figure 21-11 shows tempdb free space and version store size. As you see, as soon as the deletion starts, version
store grows and takes up all of the space from tempdb.
Figure 21-11. tempdb free space and version store size
In Figure 21-12 , you can see the version store generation and cleanup rate. The generation rate remains more or
less the same during execution, while the cleanup task cleaned the version store after the transaction is committed.
By default, the cleanup task runs once per minute as well as before an auto-growth event, in case tempdb is full.