Partilhar 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 AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Microsoft Fabric

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

Examine o tamanho do PVS

Use o sys.dm_tran_persistent_version_store_stats DMV para identificar se o tamanho do armazenamento de versão persistente (PVS) é maior do que o esperado.

A consulta de exemplo a seguir mostra as informações sobre o tamanho atual do 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 do PVS em relação ao tamanho total do banco de dados. Observe qualquer diferença do tamanho PVS típico em comparação com as linhas de base observadas durante outros períodos de atividade de aplicação. O PVS é considerado grande se for significativamente maior do que a linha de base ou se estiver próximo de 50% do tamanho do banco de dados. Utilize as etapas de solução de problemas a seguir para encontrar o motivo do grande tamanho do PVS.

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

Verifique se há transações ativas de longa duração

Transações ativas de longa duração podem impedir a limpeza de PVS em bases de dados com ADR ativado. 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 longa duração, use a seguinte consulta de exemplo. 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 terminar a sessão, se for permitido. Analise 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 execução, consulte:

Verifique se há verificações de snapshot ativas de longa duração

Varreduras de snapshots em execução de longa duração podem impedir a limpeza PVS em bases de dados com ADR ativado. As instruções que utilizam o isolamento de instantâneo (RCSI) READ COMMITTED ou os níveis de isolamento SNAPSHOT recebem carimbos de data/hora no nível da instância. Uma verificação instantânea usa o carimbo de data/hora para decidir a visibilidade da linha de versão para a transação RCSI ou SNAPSHOT. Cada instrução que utiliza RCSI tem o seu próprio carimbo de data/hora, enquanto que o isolamento SNAPSHOT tem um carimbo de data/hora ao nível de transação.

Esses carimbos de data/hora de transação no nível de instância são usados até mesmo em transações de banco de dados único, porque qualquer transação pode solicitar uma transação entre bancos de dados. Os scans de instantâneo podem, portanto, impedir a limpeza do PVS em qualquer base de dados na mesma instância do motor de base de dados. Da mesma forma, quando o ADR não está ativado, as análises de instantâneo podem impedir a limpeza do armazenamento de versão no tempdb. Como resultado, o PVS pode crescer em tamanho quando transações de longa duração que usam SNAPSHOT ou RCSI estão presentes.

Na consulta de diagnóstico 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 longa análise de instantâneo. Se esta coluna mostrar um valor maior do que o normal, isso significa que uma longa análise de snapshot está a impedir a limpeza do PVS.

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

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

Verifique se há consultas de longa execução em réplicas secundárias

Se o banco de dados tiver réplicas secundárias, verifique se o ponto de referência baixo da réplica secundária está avançando.

Execute os seguintes DMVs na réplica primária para identificar consultas de longa execução na réplica secundária que possam estar impedindo a limpeza do PVS:

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

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

Verifique se há um grande número de transações abortadas

Se nenhum dos cenários anteriores se aplicar às suas cargas de trabalho, é provável que a limpeza seja realizada 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 estar a mover-se para cima depois que a limpeza da transação abortada for concluída. Se o oldest_aborted_transaction_id for muito menor do que o oldest_active_transaction_ide o current_abort_transaction_count tiver um valor maior, é provável que uma transação abortada antiga esteja a impedir a purga do PVS.

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

  • Se possível, pare a carga de trabalho para permitir que a versão mais limpa progrida.
  • Otimize a carga de trabalho para reduzir bloqueios no nível do objeto.
  • Analise o aplicativo para identificar o problema de alta taxa de interrupção de transações. Os abortamentos podem vir de uma alta taxa de deadlocks, chaves duplicadas, violações de restrição ou tempos limite de consulta.
  • Se estiver usando o SQL Server, desabilite o ADR como uma etapa somente de emergência para controlar o tamanho do PVS. Consulte Desativar ADR.
  • Se a limpeza da transação abortada não tiver sido concluída com êxito recentemente, verifique no log de erros se há mensagens relatando problemas VersionCleaner.
  • Se o tamanho do PVS não for reduzido como esperado, mesmo após concluir a limpeza, verifique a coluna pvs_off_row_page_skipped_oldest_aborted_xdesid. Grandes valores indicam que o espaço ainda está sendo usado por versões de linha de transações abortadas.

Iniciar o processo de limpeza PVS manualmente

Se tiver uma carga de trabalho com um alto volume de declarações DML (INSERT, UPDATE, DELETE, MERGE), como OLTP de alto volume, pode exigir um período de repouso/recuperação para o processo de limpeza do PVS recuperar espaço.

Para ativar o processo de limpeza 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 do 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 evento estendido tx_mtvc2_sweep_stats.

Conteúdo relacionado