Tag Archives: SQL

MERGE, OUTPUT and $Action

19 May

Recently I’ve been making a lot of use of the OUTPUT option for insert and update actions. From this work I’ve already posted about the behaviour of IDENTITY and suchlike.
Now I have looked at the behaviour of OUTPUT within the MERGE command.
Continue reading

Primary Key With Non Clustered Index

28 Apr

As a default, SQL Server creates a Primary Key column with a Clustered Index. However, this doesn’t have to be the case.
Continue reading

The Joys of @@IDENTITY, Scope_Identity and IDENT_CURRENT

31 Mar

For several years I have happily relied on @@IDENTITY to return the latest identity of a row when inserting data. However, I have started using the OUTPUT clause and have discovered the entertainment to be had in getting the latest identity when writing to two or more tables with what can appear to be one command. Continue reading

LDAP – Using SQL to get Active Directory details – Basic Examples

14 Jul

Recently I have had to extract user’s details from Active Directory (AD) for certain security groups. Having looked through a slew of internet resources it is obvious that the work required to do this has changed little over many years – and it looks like is was deliberately designed to make it difficult. I’d like to think it wasn’t but, that is how it looks. Continue reading

Partitioned Views

17 Dec

Partitioned views have been around since before SQL Server 2005 (whereupon partitioned tables were introduced) but this does not mean that they aren’t without their uses now.
Continue reading

Forwarding Pointers and Fragmentation on a Heap Table

3 Dec

A heap is a table that has no clustered index – it may have non-clustered indexes but without a clustered index it is a heap.
When a row is updated within the heap, if the new data size means a row cannot be stored in the original location, SQL Server moves the row to another location and leaves a pointer (called a forwarding record) to show where it has been moved to.

Continue reading

Monitoring Versioning

11 Sep

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;


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 
	UPDATE dbo.test4
	SET Test_Value = Test_Value + 1
		WHERE Index_Column IN ('A', 'B')
		  AND ID < 100000;

	WAITFOR DELAY '00:00:01'

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


	FROM dbo.Test4
	WHERE Index_Column IN ('A', 'B')
	  AND ID < 100000


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.



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

Row Versioning Resource Usage

Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions)

Transaction Related Dynamic Management Views and Functions