查找正在填滿版本儲存的事務

我們已經為我們的一些 SQL Server 2005 數據庫啟用了“READ_COMMITTED_SNAPSHOT”。

現在我們不時看到我們的 TempDB 正在填滿硬碟，我們懷疑版本儲存是罪魁禍首。

我們通過監控 TempDB 使用情況sys.dm_db_file_space_usage，一旦我們看到版本儲存正在增加（如 報告 version_store_reserved_page_count），我們希望辨識正在積極使用版本儲存的事務。

我正在使用以下語句來查找使用版本儲存的事務：

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
;

但這並不能幫助我確定每個事務在版本儲存中實際使用了多少空間。

有沒有辦法獲取有關每個事務（甚至更好：每個語句）版本儲存中的空間使用情況的資訊？

編輯：“潛在重複”（如何辨識哪個查詢正在填充 tempdb 事務日誌？）不考慮版本儲存（僅臨時表、表變數以及用於排序和雜湊操作的空間）。

實際上，接受的解決方案不會顯示僅使用版本儲存的交易（至少對我而言）

按會話、事務或查詢跟踪版本儲存實際上沒有意義。如果兩個不同的使用者使用同一版本的行/表，誰擁有它？

不過，您可以按對象進行跟踪，這可以幫助您縮小導致流失的模組的範圍。看看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);

在 SQL Server 2008+ 上，您還可以通過添加以下內容來確定哪些模組引用了這些表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;

這假定臨時查詢不能創建任何版本儲存。但是，它並沒有告訴您哪些模組可能導致它 - 希望命名方案是合乎邏輯的並且可以幫助您縮小範圍。

（在 2005 年，您可能可以通過sysdepends其他舊式依賴項視圖，但我不能 100% 確定這將是多麼可靠。）

引用自：https://dba.stackexchange.com/questions/36382