SQL Server – Objects producing the most versions in version store

Hi Friends,

Many of you already know about the new isolation level in SQL Server – Snapshot. With snapshot isolation level, SQL Server now has support for Optimistic concurrency, where writers will not block readers. In a very simplified explanation, after Snapshopt Isolation is enabled on a database, if any transaction is modifying data, it has to store the most recent committed version in the version store which is maintained in tempdb database. Other transactions, if they don’t want to be blocked, can read the data from the version store, provided they are running in Snapshot Isolation level. Hold on for a second, a small example in coming below. But this blog post is not about simply explaining a troubleshooting scenario with version store.

In one of recent trainings, the participants were facing a teething issue in their production environment. The temdb version store was getting full. Error message being 3959, the version store is getting full. The reason this happens is because every update will store a last committed version in the version store. Furthermore, old data reader transactions have to traverse a longer linked list (version store is maintained in the form of a linked list). So clearly, tempdb needs to have sufficient space to avoid the above error message. You can run the following DMV to see the versions in the version store

-- Check row versions in tempdb
SELECT * FROM sys.dm_tran_version_store;
GO

But the problem does not end here. The DBAs also wanted to know which database is producing the most versions in the version store so that they can figure a course of action. You can use the following DMV for that:

select * from sys.dm_tran_top_version_generators

Now, obviously when you run the above DMVs, you will get empty result sets in case Snapshot Isolation is not enabled in your databases and there is no query running. Let us see a small example which you can use to re-produce what I have talked about.

First,  enable Snapshot Isolation in Northwind database:

USE Northwind
GO
 
ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

Next, create a new query window (new connection) and execute the following code:

-- Connection 1
SET NOCOUNT ON;
USE Northwind;
GO
BEGIN TRAN
  UPDATE dbo.Employees SET FirstName = 'Yeddureppa' WHERE EmployeeID = 1;
  SELECT FirstName FROM dbo.Employees WHERE EmployeeID = 1;
GO

From the previous connection, run the following code to see the record in the version store:

-- Check row versions in tempdb
SELECT * FROM sys.dm_tran_version_store;
GO

You shall be seeing one record, meaning that the transaction in Connection 1 has store this last committed version of the row in tempdb.

If you now want to access this row, in other words, if you want to now read this row from another transaction, you can run that transaction as follows:

-- Connection 2
SET NOCOUNT ON;
USE Northwind;
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
  SELECT FirstName FROM dbo.Employees WHERE EmployeeID = 1;
GO

Remember, in the default scenario of READ COMMITTED isolation, this transaction would have been blocked by the updater transaction. Since this transaction is running in SNAPSHOPT ISOLATION, it can read the last committed version stored in the version store.

Now, coming back to the next part of the problem, where in case you have thousands of records in the version store and you want to find out which database is using max space out there; you can run the following script:

select SUM(aggregated_record_length_in_bytes) [SpaceUsed] from sys.dm_tran_top_version_generators
GROUP by database_id
ORDER by SpaceUsed DESC

These are some real good DMVs and you can have your own variations of them. Before you wind up, roll back the transactions and disable the snapshot isolation on Northwind Database.