Tracking down reason for growing version store

Question

Hello all and thank you for reading!

Im trying to find the issue for a growing and growing version store in an app database.

With the help here: Find transactions that are filling up the version store And some websearch i was able to identify the objects involved in my problems. Unfortunately the involved tables are used at trillions of locations in the appcode. So i was wondering if it is possible to track down the querys that are "somehow" involved to narrow down possible causes for the behaviour.

There are some ressources in the web that provides guideline in how to connect the versionstore-entries to transactions / queries like this one:

WITH    version_store ( [rowset_id], [bytes consumed] )
          AS ( SELECT TOP 1 [rowset_id] ,
                        SUM(CAST([record_length_first_part_in_bytes] AS bigint)
                            + CAST([record_length_second_part_in_bytes] AS bigint))
                                                          AS [bytes consumed]
               FROM     sys.dm_tran_version_store
               GROUP BY [rowset_id]
               ORDER BY SUM(CAST([record_length_first_part_in_bytes] AS bigint)
                            + CAST([record_length_second_part_in_bytes] AS bigint)) DESC
             )
    SELECT  VS.[rowset_id] ,
            VS.[bytes consumed] ,
            DB_NAME(DTVS.[database_id]) AS [database name] ,
            DTASDT.[session_id] AS session_id ,
            DES.[login_name] AS [session login] ,
            DEST.text AS [session command]
    FROM    version_store VS
            INNER JOIN sys.[dm_tran_version_store] DTVS
                         ON VS.rowset_id = DTVS.[rowset_id]
            INNER JOIN sys.[dm_tran_active_snapshot_database_transactions]
                                                                      DTASDT
                         ON DTVS.[transaction_sequence_num] =
                                           DTASDT.[transaction_sequence_num]
            INNER JOIN sys.dm_exec_connections DEC
                         ON DTASDT.[session_id] = DEC.[most_recent_session_id]
            INNER JOIN sys.[dm_exec_sessions] DES
                         ON DEC.[most_recent_session_id] = DES.[session_id]
            CROSS APPLY sys.[dm_exec_sql_text](DEC.[most_recent_sql_handle])
                                                                       DEST ;

My problem is that i can't match the active transactions or sessions on the machine with the transactions/sessions listed within sys.dm_tran_active_snapshot_database_transactions

For excample, my query

select  es.session_id, most_recent_sql_handle, most_recent_session_id, sql.*,
        st.transaction_id, sdt.*
from    sys.dm_exec_sessions es                                                 -- Get sessions for Database and App
        left join sys.dm_exec_connections ec                                    -- Get Connectiondata to fetch most recent sql handle
            ON es.session_id = ec.session_id

        CROSS APPLY sys.[dm_exec_sql_text]([most_recent_sql_handle]) as sql     -- lookup executing SQL for sql handle

        left join sys.dm_tran_session_transactions as st                        -- Try to get transactions for the session
                                                                                --      "Returns correlation information for associated transactions and sessions."
            ON st.session_id = es.session_id
        
        left join sys.dm_tran_active_transactions as tat                        --      "Returns information about transactions for the instance of SQL Server."
            ON tat.transaction_id = st.transaction_id
                
        left join sys.dm_tran_active_snapshot_database_transactions sdt         --      "In a SQL Server instance, this dynamic management view returns a virtual table for all active transactions "
                                                                                --      " that generate or potentially access row versions."
            ON (( st.session_id = sdt.session_id ) OR 
                ( st.transaction_id = sdt.transaction_id )) 

where es.database_id = 42 

returns a lot of session / transactions but non of them appears in the dm_tran_active_snapshot_database_transactions. Or looking the other direction: i can't find any of the transactions/sessions listed in dm_tran_active_snapshot_database_transactions anywhere else.

And yes, database_id 42 seems to be definitly the cause for the versionstore-growth as

SELECT DB_NAME(database_id) as 'Database Name', 
reserved_page_count, reserved_space_kb / 1024.0 AS reserved_space_mb 
FROM sys.dm_tran_version_store_space_usage ORDER BY reserved_space_kb DESC; 

clearly identifies this database.

Does anyone have some hints how to identify the queries that are involved in the versionstore-growth?

Thank you very mutch,

Andre