60GB in version store in short amount of time, allow snapshot isolation is disabled

Question

We had a situation where the version store went from zero to 60 GB in just a few minutes. It stayed high for hours and due to the performance impact of this on tempdb (disk queue was 1000), almost everything became super slow and we ended up failing over to a secondary to get back to a reasonable state of performance.

Snapshot isolation is not enabled on any of the databases, and I can see from the session captures that everything is read committed or read uncommitted. NOLOCK is used extensively (please refrain from going down this rabbit hole unless it is 100% relevant to the issue at hand) to manage blocking.

I have found references stating that online index rebuilds will use version store, but there were no rebuilds running. There was a REORGANIZE running on a 122 GB table, but according to my understanding this would not use any version store as a reorg just moves one page at a time. Additionally, this had been running for hours before the version store usage escalated, so I'm quite sure this was not causing the issue.

There were no sleeping sessions with open transactions.

I have found some other references about MARS and triggers using version store, but not seeing that those were factors here as there are only two triggers for auditing on some small user tables.

The one thing I've noticed that started at the time the version store exploded was a column data type change was started on a load table that had ~30 million rows:

ALTER TABLE dbo.some_table ALTER COLUMN UAI_ID BIGINT NOT NULL

And it was still running 5 hours later. (Please refrain from asking why this would be done as I don't know.) There have been no recent changes to the process and it hasn't caused a problem before, so I don't see that this would cause the version store to go nuts, either.

I've seen references to reviewing the "internal space" used by sessions, but these only add up to ~10 GB, so I'm not seeing a correlation there.

Prevailing wait types were page latch and CXPACKET shortly after the version store usage went high. As time went on and things started to pile up, SLEEP_TASK and CXPACKET prevailed.

What am I missing here? What other types of activity could use so much version store so fast?

Answer 1

What other types of activity could use so much version store so fast?

If the instance was acting as a readable secondary replica for an availability group, then snapshot will be used for read queries against that database - regardless of the settings or specific isolation levels requested. If there are a lot of data changes on the primary for that AG database, or really long running transactions, then the version store can grow very quickly.

Note that this could be a different database and AG from the one where performance issues were occurring - since prior to SQL Server 2019, all user databases share the same version store in tempdb.

---

Regarding the waits you encountered, one thing I learned recently that's not very intuitive is that hash spills to tempdb incur the SLEEP_TASK wait type. This shouldn't cause the version store to grow, but it lines up with the disk issues you mentioned with tempdb (disk queue being 1,000, etc).