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,