Solucionar problemas de recuperação acelerada de banco de dados

  • Artigo

Aplica-se a: SQL Server 2019 (15.x) Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Este artigo ajuda os administradores a diagnosticar problemas da ADR (recuperação acelerada de banco de dados) no SQL Server 2019 (15.x) e posterior, na Instância Gerenciada de SQL do Azure e no Banco de Dados SQL do Azure.

Examinar o PVS (armazenamento de versão persistente)

Aproveite o DMV sys.dm_tran_persistent_version_store_stats para identificar se o tamanho do PVS de ADR (recuperação acelerada de banco de dados) está crescendo mais do que o esperado e determinar qual fator está impedindo a limpeza do PVS (armazenamento de versão persistente).

Incluída no script de exemplo a seguir, há a coluna sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesid, que foi adicionada no SQL Server 2022 (16.x) e contém o número de páginas ignoradas para recuperação devido a transações anuladas mais antigas. Se o limpador de versão for lento ou invalidado, isso refletirá quantas páginas devem ser mantidas para transações anuladas.

A consulta de exemplo mostra todas as informações sobre os processos de limpeza e mostra o tamanho atual do PVS, a transação anulada mais antiga e outros detalhes:

SELECT
 db_name(pvss.database_id) AS DBName,
 pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
 100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_pct_of_database_size,
 df.total_db_size_kb/1024./1024 AS total_db_size_gb,
 pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
 pvss.current_aborted_transaction_count,
 pvss.aborted_version_cleaner_start_time,
 pvss.aborted_version_cleaner_end_time,
 dt.database_transaction_begin_time AS oldest_transaction_begin_time,
 asdt.session_id AS active_transaction_session_id,
 asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
 pvss.pvs_off_row_page_skipped_low_water_mark,
 pvss.pvs_off_row_page_skipped_min_useful_xts,
 pvss.pvs_off_row_page_skipped_oldest_aborted_xdesid -- SQL Server 2022 only
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (SELECT SUM(size*8.) AS total_db_size_kb FROM sys.database_files WHERE [state] = 0 and [type] = 0 ) AS df 
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

  1. Verifique o tamanho de pvs_pct_of_database_size, observe qualquer diferença do típico em comparação com linhas de base durante outros períodos de atividade do aplicativo. O PVS será considerado grande se for significativamente maior do que a linha de base ou se estiver perto de 50% do tamanho do banco de dados. Use as etapas a seguir como auxílio na solução de problemas para um PVS grande.

  2. Transações ativas e de longa duração em qualquer banco de dados em que o ADR esteja habilitado podem impedir a limpeza do PVS. Recupere oldest_active_transaction_id e verifique se essa transação esteve ativa por muito tempo, consultando sys.dm_tran_database_transactions com base na ID da transação. Verifique se há transações ativas de longa duração com uma consulta como a amostra abaixo, que declara variáveis para definir limites de duração ou valor de log:

    DECLARE @longTxThreshold int = 1800; --number of seconds to use as a duration threshold for long-running transactions
DECLARE @longTransactionLogBytes bigint = 2147483648; --number of bytes to use as a log amount threshold for long-running transactions

SELECT
    dbtr.database_id, 
    transess.session_id,  
    transess.transaction_id, 
    atr.name, 
    sess.login_time,  
    dbtr.database_transaction_log_bytes_used, 
    CASE
       WHEN getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) then 'DurationThresholdExceeded' 
       WHEN dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes then 'LogThresholdExceeded' 
       ELSE 'unknown' END AS Reason 
  FROM
    sys.dm_tran_active_transactions AS tr  
    INNER JOIN sys.dm_tran_session_transactions AS transess on tr.transaction_id = transess.transaction_id  
    INNER JOIN sys.dm_exec_sessions AS sess on transess.session_id = sess.session_id 
    INNER JOIN sys.dm_tran_database_transactions AS dbtr on tr.transaction_id = dbtr.transaction_id 
    INNER JOIN sys.dm_tran_active_transactions AS atr on atr.transaction_id = transess.transaction_id 
WHERE transess.session_id <> @@spid AND 
    ( getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) OR
      dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes );

    Com as sessões identificadas, considere o fim da sessão, se permitido. Além disso, revise o aplicativo para determinar a natureza das transações ativas problemáticas.

    Para obter mais informações sobre como solucionar problemas de consultas de longa duração, consulte:

  1. A limpeza de versão persistente pode ser mantida devido a uma longa verificação de instantâneo ativo. Instruções que usam isolamento de instantâneo confirmado por leitura (RCSI) ou níveis de isolamento SNAPSHOT recebem carimbos de data/hora em nível de instância. Uma verificação de instantâneo usa o carimbo de data/hora para decidir a visibilidade da linha para a transação RCSI ou SNAPSHOT no PVS em que a recuperação acelerada do banco de dados está habilitada. Cada instrução que usa RCSI tem seu próprio carimbo de data/hora, enquanto o isolamento SNAPSHOT tem um carimbo de data/hora no nível da transação. Esses carimbos de data/hora de transação em nível de instância são usados mesmo em transações de banco de dados único, pois a transação pode ser promovida para uma transação entre bancos de dados. Assim, as verificações de instantâneo podem impedir a limpeza de registros no PVS do ADR ou, quando o ADR não está presente, no armazenamento de versão tempdb. Portanto, devido a esse rastreamento de versão, transações de longa execução que usam SNAPSHOT ou RCSI podem fazer com que o ADR PVS atrase a limpeza no banco de dados da instância, aumentando o tamanho do ADR PVS.

    Na consulta de solução de problemas original anteriormente neste artigo, o valor pvs_off_row_page_skipped_min_useful_xts mostra o número de páginas ignoradas para recuperação devido a uma longa verificação de instantâneo. Se pvs_off_row_page_skipped_min_useful_xts mostrar um valor maior do que o normal, isso significa que há uma verificação de instantâneo longa impedindo a limpeza de PVS.

    Esta consulta de exemplo pode ser usada para descobrir qual é a sessão problemática:

    SELECT 
    snap.transaction_id, snap.transaction_sequence_num, session.session_id, session.login_time, 
    GETUTCDATE() as [now], session.host_name, session.program_name, session.login_name, session.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS snap
INNER JOIN sys.dm_exec_sessions AS session ON snap.session_id = session.session_id  
ORDER BY snap.transaction_sequence_num asc;

    Para evitar atrasos na limpeza do PVS:

    1. Considere encerrar a sessão longa de transação ativa que está atrasando a limpeza do PVS, se possível. Transações de longa duração em qualquer banco de dados em que o ADR está habilitado podem atrasar a limpeza do ADR PVS.
    2. Ajuste consultas de longa duração para reduzir a duração da consulta e os bloqueios necessários. Para obter mais informações e diretrizes, consulte Compreender e resolver bloqueios no SQL Server ou Compreender e resolver problemas de bloqueio do Banco de Dados SQL do Azure.
    3. Examine o aplicativo para determinar a natureza da verificação de instantâneo ativo problemática. Considere um nível de isolamento diferente, como READ COMMITTED, em vez de SNAPSHOT ou READ COMMITTED SNAPSHOT, para consultas de longa execução que estão atrasando a limpeza do ADR PVS. Esse problema ocorre mais frequentemente com o nível de isolamento SNAPSHOT.
    4. Esse problema pode ocorrer no SQL Server, na Instância Gerenciada de SQL do Azure e nos pools elásticos do Banco de Dados SQL do Azure, mas não em bancos de dados SQL do Azure singleton. Nos pools elásticos do Banco de Dados SQL do Azure, considere mover bancos de dados para fora do pool elástico se tiverem consultas de longa duração que usam níveis de isolamento READ COMMIT SNAPSHOT ou SNAPSHOT.

  2. Quando o tamanho do PVS estiver crescendo devido a transações de longa execução em réplicas primárias ou secundárias, investigue as consultas de longa execução e resolva o gargalo. A DMV sys.dm_tran_aborted_transactions mostra todas as transações anuladas. Para obter mais informações, veja sys.dm_tran_aborted_transactions (Transact-SQL). A coluna nest_aborted indica que a transação foi confirmada, mas há partes que foram anuladas (pontos de salvamento ou transações aninhadas) que podem bloquear o processo de limpeza de PVS.

  3. Se o banco de dados fizer parte de um grupo de disponibilidade, verifique o secondary_low_water_mark. Isso é o mesmo que o low_water_mark_for_ghosts relatado por sys.dm_hadr_database_replica_states. Consulte sys.dm_hadr_database_replica_states para ver se uma das réplicas contém esse valor por trás, pois isso também impedirá a limpeza do PVS. A limpeza de versão é mantida devido a consultas de leitura em secundários legíveis. Tanto o SQL Server no local quanto o Azure SQL DB dão suporte a secundários legíveis. Na DMV sys.dm_tran_persistent_version_store_stats, pvs_off_row_page_skipped_low_water_mark também pode fornecer indicações de um atraso de réplica secundária. Para obter mais informações, confira sys.dm_tran_persistent_version_store_stats.

    A solução é igual à espera da verificação de instantâneo. Vá para os secundários, encontre a sessão que está em emissão da consulta longa e considere o fim da sessão, se permitido. Observe que a espera secundária não só afeta a limpeza de versão da ADR, mas também pode impedir a limpeza de registros fantasmas.

  4. Verifique min_transaction_timestamp (ou online_index_min_transaction_timestamp se o PVS online está se mantendo) e, com base nisso, verifique sys.dm_tran_active_snapshot_database_transactions para a coluna transaction_sequence_num para localizar a sessão que tem a transação de instantâneo antiga que contém a limpeza do PVS.

  5. Se nenhuma das alternativas acima se aplica, isso significa que a limpeza está sendo impedida devido a transações anuladas. Verifique pela última vez o aborted_version_cleaner_last_start_time e o aborted_version_cleaner_last_end_time para ver se a limpeza da transação anulada foi concluída. O oldest_aborted_transaction_id deve ser movido para cima depois que a limpeza da transação anulada é concluída. Se oldest_aborted_transaction_id é muito menor que oldest_active_transaction_id e current_abort_transaction_count tem um valor maior, há uma transação antiga anulada que impede a limpeza do PVS. A resolver:

    • Se possível, pare a carga de trabalho para permitir que a limpeza de versão avance.
    • Otimize a carga de trabalho para reduzir o uso de bloqueio no nível do objeto.
    • Revise o aplicativo para verificar qualquer problema de anulação de transação alta. Deadlock, chave duplicada e outras violações de restrição podem desencadear uma alta taxa de anulação.
    • Se estiver SQL Server, desabilite a ADR como uma etapa somente de emergência para controlar o tamanho do PVS e anular o número da transação. Confira Desabilitar o recurso de ADR.

  6. Se a transação anulada não foi concluída com êxito recentemente, verifique o log de erros em busca de mensagens relatando problemas de VersionCleaner.

  7. Monitore o log de erros do SQL Server para entradas 'PreallocatePVS'. Se houver entradas 'PreallocatePVS' presentes, isso significa que você pode precisar aumentar a capacidade do ADR de pré-alocar páginas para tarefas em segundo plano, pois o desempenho pode ser aprimorado quando o thread de segundo plano do ADR pré-aloca páginas suficientes e a porcentagem de alocações de PVS em primeiro plano está próxima de 0. Você pode usar o sp_configure 'ADR Preallocation Factor' para aumentar esse valor. Para obter mais informações, confira Opção de configuração de servidor de fator de pré-alocação ADR.

Iniciar o processo de limpeza de PVS manualmente

A ADR não é recomendada para ambientes de banco de dados com uma alta contagem de transações de atualização/exclusão, como OLTP de alto volume, sem um período de descanso/recuperação para o processo de limpeza do PVS reaver espaço.

Para ativar o processo de limpeza do PVS manualmente entre cargas de trabalho ou durante as janelas de manutenção, use o procedimento armazenado do sistema sys.sp_persistent_version_cleanup.

EXEC sys.sp_persistent_version_cleanup [database_name];

Por exemplo,

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Capturar falhas de limpeza

A partir do SQL Server 2022 (16.x), o SQL Server registra o comportamento de limpeza do ADR PVS no log de erros do SQL Server. Normalmente, isso resultaria em um novo evento de log registrado a cada 10 minutos.

Confira também

Próximas etapas