patternsqlMinor
TempDB Version Store cleanup
Viewed 0 times
versioncleanuptempdbstore
Problem
From what I can find the version store will only clean up versions that are older than the oldest active transaction. Question: Is the oldest transaction database specific or will SQL Server keep all versions, regardless of the database, if there is an older transaction still active, period?
Backstory - SQL Server 2005 SP4 Enterprise hosting around 40 databases. TempDB is currently 102 GB, version store is around 98 GB. One of the applications hosted on the database instance has an open transaction that is 40 days old based on sys.dm...database_transactions. Two separate large databases had extremely heavy use over the last month and we saw consistent TempDB growth coinciding with these operations. We expected some growth. We did not expect it to keep growing.
Question: Are the versions stored in TempDB's version store from these two separate databases still there because a third independent database has a connection that is 40 days old and shows an open transaction_state?
Perfmon counters: Version store is continually growing in the few hours I have tracked it this morning. Version Generation Rate AVG is around 30 kb/s, Version Cleanup rate is 0 kb/s.
Plenty of space left for TempDB, there are around 300 GB of total data files for all user databases, TempDB has grown on average 350 MB per day for each of its 8 data files since the last restart. This behavior is abnormal and investigation revealed the large version store
Answers to comment questions so as not to have a long running comment section:
Q: Why auto-growth on tempdb?
A: TempDB is set to initialize at a size we have found to be appropriate for most of the time. We allow auto-growth in order to handle abnormal database activity. We monitor auto-growth as well.
Q: How do you know the transaction is active and not just an active connection?
A: transaction_state says active in sys.dm_tran_active_snapshot_database_transactions and other stuff. Activity Monitor says each connection has 1 open transaction
Backstory - SQL Server 2005 SP4 Enterprise hosting around 40 databases. TempDB is currently 102 GB, version store is around 98 GB. One of the applications hosted on the database instance has an open transaction that is 40 days old based on sys.dm...database_transactions. Two separate large databases had extremely heavy use over the last month and we saw consistent TempDB growth coinciding with these operations. We expected some growth. We did not expect it to keep growing.
Question: Are the versions stored in TempDB's version store from these two separate databases still there because a third independent database has a connection that is 40 days old and shows an open transaction_state?
Perfmon counters: Version store is continually growing in the few hours I have tracked it this morning. Version Generation Rate AVG is around 30 kb/s, Version Cleanup rate is 0 kb/s.
Plenty of space left for TempDB, there are around 300 GB of total data files for all user databases, TempDB has grown on average 350 MB per day for each of its 8 data files since the last restart. This behavior is abnormal and investigation revealed the large version store
Answers to comment questions so as not to have a long running comment section:
Q: Why auto-growth on tempdb?
A: TempDB is set to initialize at a size we have found to be appropriate for most of the time. We allow auto-growth in order to handle abnormal database activity. We monitor auto-growth as well.
Q: How do you know the transaction is active and not just an active connection?
A: transaction_state says active in sys.dm_tran_active_snapshot_database_transactions and other stuff. Activity Monitor says each connection has 1 open transaction
Solution
Is the oldest transaction database specific or will SQL Server keep all versions, regardless of the database, if there is an older transaction still active, period?
SQL Server ensures that it keeps all row versions that might be needed.
To expand on that last point, an ASU can only be removed when:
For more details, see the following resources:
Row Versioning Resource Usage - MSDN
Working with tempdb in SQL Server 2005
A series of blog articles by Sunil Argawal from the SQL Server Storage Engine Team:
Version Store Basics
Version Store Logical Structure
Version Store Growth and Removing Stale Versions
SQL Server ensures that it keeps all row versions that might be needed.
- The version store is shared between all databases on the instance.
- The version store is made up of a number of "append only" storage units (ASUs).
- A new ASU is created every minute, if needed.
- A new ASU is not created if no row versions are generated.
- The current ASU is associated with a transaction when it starts.
- A transaction continues to write row versions to the same ASU until it completes.
- An ASU will generally contain row versions from many sessions, databases, tables, and indexes.
- Individual row versions are not removed - only complete ASUs are.
- An ASU is only removed when SQL Server can guarantee it is no longer needed.
- ASU cleanup is performed by a background thread that wakes up every minute.
To expand on that last point, an ASU can only be removed when:
- All transactions targeting that ASU have completed.
- All transactions that might need versions from that ASU have completed.
- All earlier ASUs have been removed.
For more details, see the following resources:
Row Versioning Resource Usage - MSDN
Working with tempdb in SQL Server 2005
A series of blog articles by Sunil Argawal from the SQL Server Storage Engine Team:
Version Store Basics
Version Store Logical Structure
Version Store Growth and Removing Stale Versions
Context
StackExchange Database Administrators Q#45934, answer score: 7
Revisions (0)
No revisions yet.