Partilhar via

Monitorar e 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 você a monitorar, diagnosticar e resolver problemas com de recuperação acelerada de banco de dados 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.

O exemplo de consulta de diagnóstico a seguir mostra as informações sobre o tamanho atual do PVS, os processos de limpeza e outros detalhes em todos os bancos de dados em que o tamanho do PVS é maior que zero:

SELECT pvss.database_id,
       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_percent_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,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       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.master_files AS mf
            WHERE mf.database_id = pvss.database_id
                  AND
                  mf.state = 0
                  AND
                  mf.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.persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

Verifique a coluna pvs_percent_of_database_size para ver o tamanho do PVS em relação ao tamanho total do banco de dados. Observe qualquer diferença entre o tamanho típico do PVS e as linhas de base observadas durante os períodos típicos da 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.

Se o tamanho do PVS não estiver diminuindo, use as etapas de solução de problemas a seguir para localizar e resolver o motivo do tamanho grande do PVS.

Dica

As colunas mencionadas nas etapas de solução de problemas a seguir referem-se às colunas no conjunto de resultados da consulta de diagnóstico nesta seção.

O tamanho grande do PVS pode ser causado por qualquer um dos seguintes motivos:

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 localizar transações de longa duração, 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 = 900;  /* 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 the generated log threshold for long-running transactions */

SELECT  dbtr.database_id,
        DB_NAME(dbtr.database_id) AS database_name,
        st.session_id,
        st.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 st
ON tr.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS sess
ON st.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 = st.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 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 varredura instantânea usa o carimbo de data/hora para determinar a visibilidade da versão de linha 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 ser promovida para 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, mesmo quando o ADR não está ativado, as verificações instantâneas 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.

A coluna pvs_off_row_page_skipped_min_useful_xts mostra o número de páginas ignoradas durante a limpeza devido a uma extensa verificação de instantâneo. Se esta coluna mostrar um valor elevado, isso significa que uma longa varredura de instantâneo está a impedir a limpeza do PVS.

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

SELECT sdt.transaction_id,
       sdt.transaction_sequence_num,
       s.database_id,
       s.session_id,
       s.login_time,
       GETDATE() AS query_time,
       s.host_name,
       s.program_name,
       s.login_name,
       s.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS sdt
INNER JOIN sys.dm_exec_sessions AS s
ON sdt.session_id = s.session_id;

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 para evitar o atraso de limpeza do PVS em outros bancos de dados no mesmo pool.

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.

Um valor elevado na coluna pvs_off_row_page_skipped_low_water_mark pode ser uma indicação de um atraso de limpeza devido a uma consulta prolongada numa réplica secundária. Além de atrasar a limpeza do PVS, um consulta de longa duração numa réplica secundária também pode atrasar a limpeza fantasma.

Você pode usar as seguintes consultas de exemplo na réplica primária para verificar se consultas de longa duração em réplicas secundárias podem estar impedindo a limpeza do PVS. Se uma carga de trabalho de gravação estiver sendo executada na réplica primária, mas o valor na coluna low_water_mark_for_ghosts não estiver aumentando de uma execução da consulta de exemplo para a próxima, o PVS e a limpeza fantasma poderão ser mantidos por uma consulta de longa execução em uma réplica secundária.

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       low_water_mark_for_ghosts,
       synchronization_state_desc,
       synchronization_health_desc,
       is_suspended,
       suspend_reason_desc,
       secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
      AND
      is_primary_replica = 1;

Para obter mais informações, consulte a descrição da coluna low_water_mark_for_ghosts no sys.dm_hadr_database_replica_states.

Conecte-se a cada réplica secundária acessível para leitura, localize a sessão com a consulta de longa duração e considere encerrar a sessão, se permitido. Para obter mais informações, consulte Localizar consultas lentas.

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

Verifique as colunas aborted_version_cleaner_start_time e aborted_version_cleaner_end_time para confirmar se a última eliminação das transações abortadas já 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 oldest_active_transaction_ide o valor current_abort_transaction_count for grande, é provável que haja uma transação abortada antiga impedindo a limpeza do PVS.

Para resolver o atraso de limpeza do PVS devido a um grande número de transações abortadas, considere o seguinte:

  • Se você estiver usando o SQL Server 2022 (16.x), aumente o valor da configuração do servidor ADR Cleaner Thread Count. Para obter mais informações, consulte Configuração do servidor : Contagem de threads do Limpador ADR.
  • Se possível, pare a carga de trabalho para permitir que a versão mais limpa progrida.
  • Revise o aplicativo para identificar e resolver o problema da 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.
  • Otimize a carga de trabalho para reduzir bloqueios que são incompatíveis com os bloqueios IX ao nível do objeto ou da partição exigidos pelo limpador PVS. Para obter mais informações, consulte Compatibilidade de bloqueio.
  • 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 estiver usando o SQL Server e se a limpeza de transação abortada não tiver sido concluída com êxito recentemente, verifique se há mensagens relatando problemas VersionCleaner no log de erros.
  • 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.

Controle o tamanho do PVS

Se tiveres uma carga de trabalho com um alto volume de instruções DML (INSERT, UPDATE, DELETE, MERGE), como OLTP de elevado volume, e observares que o tamanho do PVS é grande, poderá ser necessário aumentar o valor da configuração do servidor ADR Cleaner Thread Count para manter o tamanho do PVS sob controlo. Para obter mais informações, consulte Configuração do Server: ADR Cleaner Thread Count, que está disponível a partir do SQL Server 2022 (16.x).

No SQL Server 2019 (15.x), ou se o aumento do valor da configuração ADR Cleaner Thread Count não ajudar a reduzir o tamanho do PVS suficientemente, a carga de trabalho pode exigir um período de repouso/recuperação para que o processo de limpeza do PVS recupere 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), mensagens notáveis de limpeza do PVS são registradas no log de erros. As estatísticas de limpeza também são relatadas pelo evento estendido tx_mtvc2_sweep_stats.

Conteúdo relacionado