Find transactions that are filling up the version store

Question

we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases.

Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be the culprit.

We monitor the TempDB usage through sys.dm_db_file_space_usage and once we see that the version store is increasing (as reported by version_store_reserved_page_count) we would like to identity the transactions that are activily using the version store.

I'm using the following statement to find transactions that are using the version store:

SELECT db_name(spu.database_id) as database_name,
       at.transaction_begin_time as begin_time,
       case 
         when at.transaction_state in (0,1) then 'init'
         when at.transaction_state = 2 then 'active'
         when at.transaction_state = 3 then 'ended'
         when at.transaction_state = 4 then 'committing'
         when at.transaction_state = 6 then 'comitted'
         when at.transaction_state = 7 then 'rolling back'
         when at.transaction_state = 6 then 'rolled back'
         else 'other'
       end as transaction_state,
       ast.elapsed_time_seconds as elapsed_seconds,
       ses.program_name, 
       ses.row_count,
       (spu.user_objects_alloc_page_count * 8) AS user_objects_kb,
       (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb,
       (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb,
       (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb
FROM sys.dm_tran_active_snapshot_database_transactions ast
  JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id
  JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
  JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
ORDER BY elapsed_time_seconds DESC
;

But this doesn't help me in identifying how much space each transaction is actually using in the version store.

Is there a way to get the information about the space usage in the version store per transaction (or even better: per statement)?

Edit: The "potential duplicate" (How to identify which query is filling up the tempdb transaction log?) does not take the version store into account (only temp tables, table variables, and space used for sort and hash operations).

In fact the accepted solution will not show anything for transactions that only use the version store (at least not for me)

Answer 1

It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it?

You can track this by object, though, which can help you narrow down which modules are causing the churn. Have a look at sys.dm_tran_top_version_generators:

USE [your database];
GO
SELECT obj = 
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  vs.aggregated_record_length_in_bytes
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1);

And on SQL Server 2008+, you can also figure out which modules reference these tables by adding sys.dm_sql_referencing_entities:

SELECT 
  obj = QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  referenced_by = QUOTENAME(r.referencing_schema_name)
  + '.' + QUOTENAME(r.referencing_entity_name),
  vs.aggregated_record_length_in_bytes AS size
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
CROSS APPLY sys.dm_sql_referencing_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)), 'OBJECT'
) AS r
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1)
ORDER BY size DESC, referenced_by;

This assumes that none of the version store could be created by ad hoc queries. However, it doesn't tell you which of those modules could be causing it - hopefully the naming scheme is logical and helps you narrow it down a bit.

(On 2005 you might be able to go through sysdepends and other old-style dependency views but I'm not 100% sure how reliable that would be.)

Answer 2

As Aaron Bertrand's answer to this question is one of the most useful ones I found regarding this topic, I want to share my experience:

For me dm_tran_top_version_generators generates arithmetic overflows for integer as some version generators exceed 2GB I guess. So, I had to modify Aaron's query a bit and did a workaround for dm_tran_top_version_generators in the form of a CTE:

WITH dm_tran_top_version_generators_big AS (
    select top 256  database_id, rowset_id, 
    SUM(CAST([record_length_first_part_in_bytes] AS bigint) + 
        CAST([record_length_second_part_in_bytes] AS bigint)) AS aggregated_record_length_in_bytes  
    from sys.dm_tran_version_store
    GROUP BY database_id, rowset_id
    ORDER BY aggregated_record_length_in_bytes DESC
)
SELECT 
  obj = QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  referenced_by = QUOTENAME(r.referencing_schema_name)
  + '.' + QUOTENAME(r.referencing_entity_name),
  r.*, p.*, 
  vs.aggregated_record_length_in_bytes AS size
FROM dm_tran_top_version_generators_big AS vs
INNER JOIN sys.partitions AS p ON vs.rowset_id = p.hobt_id
CROSS APPLY sys.dm_sql_referencing_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)), 'OBJECT'
) AS r
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1)
ORDER BY size DESC, referenced_by;

Thank you very much Aaron!