Here’s one I stumbled across recently – I was monitoring tempdb space and could see some version store usage on a database that did not have snapshot or Read Committed Snapshot Isolation enabled, I was puzzled by this, but eventually found the answer.

What is the Version Store?

As a quick primer, the version store in SQL Server is one of the many features that use tempdb. When Snapshot or Read Committed Snapshot Isolation (RCSI) is enabled, any transactions that change (UPDATE / DELETE) data will create a copy of the rows before they are modified and store them in tempdb. The reason for this behaviour is that if a reading transaction wants to read the data that is being changed by the writing transaction, it will read the copy that is written to tempdb – the query is not blocked and concurrency is improved.

The Problem

As I mentioned earlier, I could see the size of the version store had been fluctuating over time on a database that did not have Snapshot or RCSI enabled, which I found odd. However, I managed to find this post, which explains that SQL Server uses the version store to support the inserted and deleted virtual tables that are available in a trigger.

The Demo

Let’s take this fairly simple query. I am using the StackOverflow database provided under cc-by-sa 4.0 license from Stack Exchange Data Dump, this will work on any version of the database, though I am using 2010. I am using a SQL Server 2022 instance.

The query updates everyone’s reputation to 1 (sorry, everyone!)

BEGIN TRAN
	UPDATE	dbo.Users
	SET	Reputation = 1;

I’ve left the transaction open to make it easier to observe what is happening in the version store as the inbuilt garbage collector will not purge any version store data no longer needed while there is an open transaction.

We can now observe the size of the version store using:

SELECT * FROM sys.dm_tran_version_store_space_usage WHERE database_id = DB_ID('StackOverflow2010');

We can see zero pages are in the version store.

Let’s rollback our update and create this deliberately trivial trigger on the Users table:

CREATE TRIGGER trg_Silly ON dbo.Users
AFTER UPDATE
AS
	SELECT 1;
GO

and run our update again:

BEGIN TRAN
	UPDATE	dbo.Users
	SET	Reputation = 1;

Then look again at the version store:

We can see it is being used! Just to confirm I have no tricks up my sleeve, let’s confirm the state of the snapshot isolation levels:

SELECT	is_read_committed_snapshot_on,
		snapshot_isolation_state_desc
FROM	sys.databases
WHERE   name = 'StackOverflow2010';

If I rollback my update and re-run my version store query a few times (it seems to take around a minute for the garbage collection)

SELECT * FROM sys.dm_tran_version_store_space_usage WHERE database_id = DB_ID('StackOverflow2010');

We can see that the version store is now clear.

To use the version store, the trigger must be an AFTER UPDATE /I NSERT / DELETE trigger rather than an INSTEAD OF trigger, I believe this is because an INSTEAD OF trigger effectively throws away the DML that was issued.

This behaviour has been in place since SQL Server 2005, which is when the version store was introduced. Prior to that, the deleted / inserted tables used the transaction log.

Accelerated Database Recovery

Accelerated Database Recovery (ADR) introduced in SQL Server 2019 changes the behaviour of the version store on the databases for which it is enabled – the version store is part of the database itself (known as the Persistent Version Store or PVS) so does this affect the trigger behaviour?

Let’s test and see:

Enable ADR:

ALTER DATABASE StackOverflow2010 SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE;

and run the same query and check the version store in the same way:

BEGIN TRAN
	UPDATE	dbo.Users
	SET	Reputation = 1;

SELECT * FROM sys.dm_tran_version_store_space_usage WHERE database_id = DB_ID('StackOverflow2010');

It doesn’t look to be using the version store as everything is at 0.

There is a DMV to monitor the PVS, so let’s look at that:

SELECT * FROM sys.dm_tran_persistent_version_store_stats WHERE database_id = DB_ID('StackOverflow2010');

Sure enough, it looks to be using the PVS. Let’s rollback the transaction, wait a minute or so and check again.

The value wasn’t going down, so I had to run the following procedure to trigger a manual cleanup of the PVS:

EXEC sp_persistent_version_cleanup

Why I had to run the cleanup is probably out of scope for this article, we did, however, prove that the PVS is used to support the trigger when ADR is enabled, rather than the tempdb version store.

Conclusion

We have seen that an AFTER trigger uses the version store whether or not a snapshot isolation level is enabled on the database, however when ADR is enabled, this behaviour shifts to the Persistent Version Store (PVS) within the database itself, rather than the version store in tempdb.

References / Further Reading

Paul White – Instead of Triggers

Kalen Delaney – Triggers and the Version Store

Sunil Agarwal – Managing TempDB in SQL Server: TempDB Basics (Version Store: Why do we need it?)