содержимое
My Version Store is huge!!!!
Are you using Read Committed Snapshot isoloation? We are and it is blowing up our TempDB.

I began to investigate why TempDB is growing so large especially on some of my production servers. I found that our main prod databases are using Read Committed Snapshot Isolation or (RCSI). This isolation level changes the type of concurrency that SQL Server will use. By default SQL Server uses pessimistic locking mean writers block readers and readers block writers. RCSI is optimistic blocking, much like Oracle or Postgres. RCSI creates a version store.
The version store contains the committed rows which is how a SELECT operation does not get blocked when another UPDATE/DELETE is operating on the same row, because the SELECT reads the row from the version store, instead of the actual base table. When you enable this, the row has to be stored somewhere and tempdb happens to be the place. A row is maintained in the version store when there are transactions operating on that row in question. When the transaction is committed, the row is cleaned up from the version store tables.


Row versions must be stored for as long as an active transaction needs to access it. Once every minute, a background thread removes row versions that are no longer needed and frees up the version space in tempdb. A long-running transaction prevents space in the version store from being released if it meets any of the following conditions:
• It uses row versioning-based isolation.
• It uses triggers, MARS, or online index build operations.
• It generates row versions.


We are using two of the three conditions .


The below query tells us how big the Version Store has grown. The Version store is 557 GBs which is over 10 times large than the prod database. I tried to do a row count to see how many rows were in the version store. I let it run for a couple hours and it never returned any results.

SQL Dude helped with some of this content
http://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

SELECT
SUM (user_object_reserved_page_count)*8/1024.0/1024.0 as user_obj_GB,
SUM (internal_object_reserved_page_count)*8/1024.0/1024.0 as internal_obj_GB,
SUM (version_store_reserved_page_count)*8/1024.0/1024.0 as version_store_GB,
SUM (unallocated_extent_page_count)*8/1024.0/1024.0 as freespace_GB,
SUM (mixed_extent_page_count)*8/1024.0/1024.0 as mixedextent_GB
FROM sys.dm_db_file_space_usage

Continuing to look into the issue, I kept seeing queries with this statement, Implicit_Transactions. The default for SQL Server is Set Implicit_Transactions off. Below is from Microsoft Books Online regarding this issue.


When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
ALTER TABLE
FETCH
REVOKE
BEGIN TRANSACTION
GRANT
SELECT
CREATE
INSERT
TRUNCATE TABLE
DELETE
OPEN
UPDATE
DROP


I wanted to see if there any active transations. This is tricky when using RCSI as sometime active transactions will not show up with the usual tools such as
DBCC opentran


The below query shows us active transactions and active transactions being used by RCSI.

select
t.transaction_id,t.name,t.transaction_type, t.transaction_state,
s.transaction_id,s.session_id,
s.elapsed_time_seconds/60/60.0 as hours_tran_has_been_open, p.status, p.cmd
from sys.dm_tran_active_transactions t
join sys.dm_tran_active_snapshot_database_transactions s
on t.transaction_id = s.transaction_id
join sys.sysprocesses p
on p.spid = s.session_id

If Implicit_Transactions is being set to ON and connections to the database are not being closed this will cause the version store to grow which causes TempDB to grow which will either fill the disk or cause performance issue being that the version store is so large.

Here is another great article on this

https://www.red-gate.com/simple-talk/sql/performance/read-committed-snapshot-isolation-high-version_ghost_record_count/