Início / dba / Perguntas / 36382
Accepted
a_horse_with_no_name
Asked: 2013-03-12 05:43:17 +0800 CST

Encontre transações que estão preenchendo o armazenamento de versão

  • 772

habilitamos o "READ_COMMITTED_SNAPSHOT" para alguns de nossos bancos de dados SQL Server 2005.

Agora, de tempos em tempos, vemos que nosso TempDB está enchendo o disco rígido e suspeitamos que o armazenamento de versão seja o culpado.

Monitoramos o uso do TempDB sys.dm_db_file_space_usagee, quando vemos que o armazenamento de versão está aumentando (conforme relatado por version_store_reserved_page_count), gostaríamos de identificar as transações que estão usando ativamente o armazenamento de versão.

Estou usando a seguinte declaração para encontrar transações que estão usando o armazenamento de versão:

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
;

Mas isso não me ajuda a identificar quanto espaço cada transação está realmente usando no armazenamento de versão.

Existe uma maneira de obter as informações sobre o uso de espaço no armazenamento de versão por transação (ou melhor ainda: por instrução)?

Editar: A "duplicata potencial" ( Como identificar qual consulta está preenchendo o log de transações do tempdb? ) não leva em consideração o armazenamento de versão (apenas tabelas temporárias, variáveis ​​de tabela e espaço usado para operações de classificação e hash).

Na verdade, a solução aceita não mostrará nada para transações que usam apenas o armazenamento de versão (pelo menos não para mim)

sql-server sql-server-2005

1 respostas

  1. Best Answer

    Realmente não faz sentido rastrear o armazenamento de versão por sessão, transação ou consulta. Se dois usuários diferentes estiverem usando a mesma versão de uma linha/tabela, quem é o proprietário?

    No entanto, você pode rastrear isso por objeto, o que pode ajudá-lo a restringir quais módulos estão causando a rotatividade. Dê uma olhada em 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);

    E no SQL Server 2008+, você também pode descobrir quais módulos fazem referência a essas tabelas adicionando 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;

    Isso pressupõe que nenhum armazenamento de versão pode ser criado por consultas ad hoc. No entanto, ele não informa qual desses módulos pode estar causando isso - esperamos que o esquema de nomenclatura seja lógico e ajude a reduzi-lo um pouco.

    (Em 2005, você pode passar por sysdependsoutras exibições de dependência de estilo antigo, mas não tenho 100% de certeza de quão confiável isso seria .)

    • 12

relate perguntas