主页 / dba / 问题 / 36382
Accepted
a_horse_with_no_name
Asked: 2013-03-12 05:43:17 +0800 CST

查找正在填满版本存储的事务

  • 772

我们已经为我们的一些 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 事务日志？）不考虑版本存储（仅临时表、表变量以及用于排序和哈希操作的空间）。

实际上，接受的解决方案不会显示仅使用版本存储的交易（至少对我而言）

sql-server sql-server-2005

1 个回答

  1. Best Answer

    按会话、事务或查询跟踪版本存储实际上没有意义。如果两个不同的用户使用同一版本的行/表，谁拥有它？

    不过，您可以按对象进行跟踪，这可以帮助您缩小导致流失的模块的范围。看看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% 确定这将是多么可靠。）

    • 12

相关问题