2

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?

3

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).

  • Thanks Josh--that is a great tidbit to know. This server was a primary for the only AG so the hunt continues. As I've been investigating further it looks like I/O started having performance issues after a McAfee update of some sort--checking into whether that was a coincidence... – Tony Hinkle Sep 16 at 18:06
  • 1
    @TonyHinkle No problem! Good luck on the hunt, sounds like the I/O issues are a strong suspect. By the way, it might be helpful to include in your question how you narrowed this down to version store activity (vs other things causing tempdb to grow). It occurred to me there might be multiple issues here when I noticed that SLEEP_TASK was one of the waits. – Josh Darnell Sep 16 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.