Compartilhar via

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

  • Artigo

Aplica-se a: SQL Server 2019 (15.x) e versões posteriores Banco de Dados SQL do Azure Banco de dados SQL da InstânciaGerenciada de SQL do Azure no Microsoft Fabric

Este artigo ajuda a diagnosticar problemas com ADR (recuperação acelerada de banco de dados) no SQL Server 2019 (15.x) e posteriores, Instância Gerenciada de SQL do Azure, Banco de Dados SQL do Azure e Banco de Dados SQL no Microsoft Fabric.

Examinar o tamanho do PVS

Utilize o sys.dm_tran_persistent_version_store_stats DMV para verificar se o tamanho do repositório de versão persistente (PVS) é maior do que o esperado.

A consulta de exemplo a seguir mostra as informações sobre o tamanho atual da PVS, os processos de limpeza e outros detalhes:

SELECT  DB_NAME(pvss.database_id) AS database_name,
        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
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();

Verifique a coluna pvs_pct_of_database_size para ver o tamanho da PVS em relação ao tamanho total do banco de dados. Observe qualquer diferença em relação ao tamanho típico de PVS em comparação com as linhas de base vistas durante outros períodos de atividade do aplicativo. O PVS é considerado grande quando é significativamente maior que a linha de base ou está perto de 50% do tamanho do banco de dados. Utilize os procedimentos de solução de problemas a seguir para encontrar o motivo para o grande tamanho da PVS.

Se o tamanho do PVS for maior do que o esperado, verifique:

Verificar se há transações ativas de execução prolongada

Transações ativas de execução prolongada podem impedir a limpeza de PVS em bancos de dados que têm ADR habilitada. Verifique a hora de início da transação ativa mais antiga usando a coluna oldest_transaction_begin_time. Para obter mais informações sobre transações de execução longa, use a consulta de exemplo a seguir. Você pode definir limites para a duração da transação e a quantidade de log de transações gerado:

DECLARE @LongTxThreshold int = 1800;  /* The number of seconds to use as a duration threshold for long-running transactions */
DECLARE @LongTransactionLogBytes bigint = 1073741824; /* The 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 GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
        OR
        dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

Com as sessões identificadas, considere o encerramento da sessão, se permitido. Examine o aplicativo para determinar a natureza das transações ativas problemáticas para evitar o problema no futuro.

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

Verificar se há varreduras prolongadas de instantâneo ativo

Varreduras de instantâneo ativo de execução prolongada podem impedir a limpeza de PVS em bancos de dados que têm ADR habilitada. Instruções que usam RCSI (isolamento de instantâneo READ COMMITTED) ou SNAPSHOTníveis de isolamento obtêm carimbos de data/hora em nível de instância. Uma varredura de instantâneo usa o carimbo de data/hora para decidir a visibilidade da linha na versão para o RCSI ou a transação SNAPSHOT. 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 no nível da instância são usados mesmo em transações de banco de dados único, pois qualquer transação pode solicitar uma transação entre bancos de dados. Portanto, as varreduras de instantâneo podem impedir a limpeza de PVS em qualquer banco de dados na mesma instância do mecanismo de banco de dados. De modo similar, quando a ADR não está habilitada, as varreduras de instantâneo podem impedir a limpeza do repositório de versão no tempdb. Como resultado, o PVS pode aumentar de tamanho quando há transações de execução prolongada que usam SNAPSHOT ou RCSI.

Em solução de problemas de consulta no início deste artigo, a coluna pvs_off_row_page_skipped_min_useful_xts mostra o número de páginas ignoradas para recuperação devido a uma varredura de instantâneo prolongada. Quando essa coluna mostra um valor maior do que o normal, isso significa que uma varredura de instantâneo prolongada está impedindo a limpeza de PVS.

Use a seguinte consulta de exemplo para localizar a sessão com a transação de RCSI ou SNAPSHOT de execução prolongada:

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:

  • Considere encerrar a sessão longa de transação ativa que está atrasando a limpeza do PVS, se possível.
  • Ajuste consultas de execução prolongada para reduzir a duração delas.
  • 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 RCSI, para consultas de execução prolongada que estão atrasando a limpeza do PVS. Esse problema ocorre com mais frequência com o nível de isolamento SNAPSHOT.
  • Nos pools elásticos do Banco de Dados SQL do Azure, considere mover para fora do pool elástico os bancos de dados que têm transações de longa execução usando isolamento SNAPSHOT ou RCSI.

Verifique consultas de execução prolongada em réplicas secundárias

Se o banco de dados tiver réplicas secundárias, verifique se a marca-d'água baixa secundária está avançando.

Execute as seguintes DMVs na réplica primária para identificar consultas de execução prolongada na réplica secundária que podem estar impedindo a limpeza de PVS:

No DMV sys.dm_tran_persistent_version_store_stats, as colunas pvs_off_row_page_skipped_low_water_mark também podem indicar um atraso na limpeza devido a uma consulta de execução prolongada em uma réplica secundária.

Conecte-se a uma réplica secundária, localize a sessão que está executando a consulta prolongada e considere encerrar a sessão, se permitido. A consulta de execução prolongada na réplica secundária pode atrasar a limpeza de PVS, bem como impedir a limpeza duplicada.

Verificar se há um grande número de transações anuladas

Se nenhum dos cenários anteriores se aplicar às suas cargas de trabalho, é provável que a limpeza seja adiada devido a um grande número de transações abortadas. Verifique as colunas aborted_version_cleaner_last_start_time e aborted_version_cleaner_last_end_time para ver se a limpeza da última transação abortada foi concluída. O oldest_aborted_transaction_id deve ser movido para cima depois que a limpeza da transação anulada é concluída. Se o oldest_aborted_transaction_id é muito menor do que oldest_active_transaction_id e current_abort_transaction_count tem um valor maior, provavelmente há uma transação anulada antiga impedindo a limpeza do PVS.

Para resolver um grande número de transações anuladas, considere o seguinte:

  • Se possível, interrompa o trabalho em andamento para permitir que o limpador de versão progrida.
  • Otimize a carga de trabalho para reduzir os bloqueios no nível do objeto.
  • Examine o aplicativo para identificar o problema de taxa de anulação de transação alta. As anulações podem vir de uma alta taxa de bloqueios, chaves duplicadas, violações de restrição ou tempos limite de consulta.
  • Se estiver usando o SQL Server, desabilite a ADR como uma etapa somente de emergência para controlar o tamanho da PVS. Confira Desabilitar ADR.
  • Se a limpeza da transação abortada não tiver sido concluída com sucesso recentemente, verifique o log de erros em busca de mensagens relatando problemas VersionCleaner.
  • Se o tamanho do PVS não for reduzido conforme esperado, mesmo após a conclusão da limpeza, verifique a coluna pvs_off_row_page_skipped_oldest_aborted_xdesid. Valores grandes indicam que o espaço ainda está sendo utilizado por versões de linhas de transações anuladas.

Iniciar o processo de limpeza de PVS manualmente

Se você tiver uma carga de trabalho com um alto volume de instruções DML (INSERT, UPDATE, DELETE, MERGE), como um alto volume de OLTP, poderá ser necessário um período de repouso ou recuperação para que o processo de limpeza do PVS consiga recuperar 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.

Por exemplo:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Capturar falhas de limpeza

A partir do SQL Server 2022 (16.x), o comportamento de limpeza de PVS é registrado no log de erros. Normalmente, isso resulta em um novo evento de log registrado a cada 10 minutos. As estatísticas de limpeza também são relatadas pelo tx_mtvc2_sweep_statsevento estendido.

Conteúdo relacionado