Notes on SQL

Optimistic locking requires the use of row versioning, where a copy of the data about to be modified is stored in TempDB.
The advantage of this approach is that the number of locks are reduced and the opportunities for blocking are also reduced.

The downside is that the resources required for data modifications are increased, because the data requires a version creating before modification and pointers need to be generated to ensure the correct versions are in use. Retrieving the correct version also incurs an additional overhead, because the version store is a linked list and therefore a number of rows may need to be traversed before finding the data required.

Because TempDB contains the row versions for all databases on an instance it needs to have enough space to accommodate these. Should TempDB run out of space then an attempt will be made to shrink the version store. This can result in error 3967, where a transaction that has yet to generate a version has been rolled back, or error 3966 where a transaction cannot read the row version. Write operations will continue, but error 3959 will be written to the error log because the write could not generate a row version.

So it really pays to ensure there is enough space for TempDB.

There are several ways to see the impact of using a version store – Performance Monitor and DMVs.

Basic Example
In this example I’m using three perfmon counters, all located under the instance of the appropriate SQL server –
Transactions\Version Generation rate (KB/s),
Transactions\Version cleanup rate (KB/s) and
Transactions\Version store size (KB).

On the instance used in this example the MSDB database has row-versioning enabled because it has snapshot isolation set. Therefore there is always some background activity in the version store.

Approximately every minute an automatic job executes which cleans out entries from the version store that are no longer required and is monitored under ‘Version cleanup rate (KB/s)’.
Similarly, the two DMVs ‘sys.dm_tran_top_version_generators’ and ‘sys.dm_tran_version_store’ show a small amount of activity within the version store.

Because select queries don’t generate row versions, executing a simple update against a test table is all that is required to demonstrate the potential impact upon the TempDB.

USE IsolationLevels;

SET ROWCOUNT 100000;

UPDATE dbo.test4
SET Test_Value = 1;

A spike can be seen in the version generation and an increase in the size of the version store.

In addition, there are a number of entries reported by the DMVs, filtered on the database in question.

The cleanup process (the green line in the following screenshot) then removes any entries from the version store that are too old to be of use.

And in this example, the DMVs return no results after the cleanup.

Now I’m going to loop the update and run a select query, which also loops. This will increase the number of versions created and acquired, showing the impact via Perfom and the DMVs.

The update

WHILE 1 =1 
BEGIN
	UPDATE dbo.test4
	SET Test_Value = Test_Value + 1
		WHERE Index_Column IN ('A', 'B')
		  AND ID < 100000;

	WAITFOR DELAY '00:00:01'
END

Will run until terminated, generating a large number of versions.

The select will run constantly too, showing the data it is reading.

WHILE 1 = 1

BEGIN

	SELECT *
	FROM dbo.Test4
	WHERE Index_Column IN ('A', 'B')
	  AND ID < 100000
	ORDER BY id ASC;


END

Perfmon shows a gradual increase in the version store size, although the rate of creation is low (due to the WAITFOR DELAY).
When the cleanup executes it does not remove all of the versions, because the SELECT statement is using some of those versions.

In addition, the DMVs return a larger number of rows and takes longer to do so. This extra time can be excessive where the version store has become large.

Conclusion

Optimistic locking can reduce the overhead of locking and blocking, but it incurs its own overheads which need to be considered.

Further Reading

Understanding Row Versioning-Based Isolation Levels
https://msdn.microsoft.com/en-us/library/ms189050(v=sql.105).aspx

Row Versioning Resource Usage
https://msdn.microsoft.com/en-us/library/ms189050(v=sql.105).aspx

Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions)
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/01/managing-tempdb-in-sql-server-tempdb-basics-version-store-growth-and-removing-stale-row-versions.aspx

Transaction Related Dynamic Management Views and Functions
https://msdn.microsoft.com/en-us/library/ms178621.aspx