Compartilhar 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 Azure Banco de dados SQL da InstânciaGerenciada de SQL do Azure no Microsoft Fabric

Este artigo ajuda você a monitorar, diagnosticar e resolver problemas com ADR (recuperação acelerada de banco de dados) no SQL Server 2019 (15.x) e posterior, 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 diagnóstico de exemplo a seguir mostra as informações sobre o tamanho atual da PVS, os processos de limpeza e outros detalhes em todos os bancos de dados em que o tamanho da 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 visualizar o tamanho da PVS em relação ao tamanho total do banco de dados. Observe qualquer diferença entre o tamanho típico de PVS e as linhas de base vistas durante períodos típicos 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.

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

Gorjeta

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

Um tamanho grande de PVS pode ser causado por qualquer um dos seguintes motivos:

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 localizar transações de execução prolongada, use a consulta de exemplo a seguir. Defina 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 o encerramento da sessão, se permitido. Examine o aplicativo para determinar a natureza das transações problemáticas para evitar o problema no futuro.

Para mais informações sobre como resolver problemas em 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 verificação de instantâneo usa o carimbo de data/hora para determinar a visibilidade da linha de 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 timestamps de transação em nível de instância são usados até mesmo em transações de banco de dados único, pois qualquer transação pode ser promovida a uma transação entre bancos de dados múltiplos. 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. Da mesma forma, mesmo quando a ADR não está habilitada, as verificações de instantâneo podem impedir a limpeza do repositório de versão em tempdb. Como resultado, o PVS pode aumentar de tamanho quando há transações de execução prolongada que usam SNAPSHOT ou RCSI.

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

Use a consulta de exemplo a seguir para localizar as sessões com a transação RCSI ou de execução prolongada SNAPSHOT:

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 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 SNAPSHOT nível de isolamento.
  • Nos pools elásticos do Banco de Dados SQL do Azure, considere mover bancos de dados que têm transações de execução prolongada usando isolamento SNAPSHOT ou RCSI, para fora do pool elástico para evitar atraso de limpeza de PVS em outros bancos de dados no mesmo pool.

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.

Um valor grande na coluna pvs_off_row_page_skipped_low_water_mark pode ser uma indicação de um atraso de limpeza devido a uma consulta de execução prolongada em uma réplica secundária. Além de manter a limpeza de PVS, uma consulta de execução prolongada em uma réplica secundária também pode conter a limpeza de fantasmas.

Use as consultas de exemplo a seguir na réplica primária para descobrir se consultas de execução prolongada em réplicas secundárias podem estar impedindo a limpeza de PVS. Se uma carga de trabalho de gravação estiver em execução 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, a PVS e a limpeza fantasma poderão ser mantidas por uma consulta de execução prolongada 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 low_water_mark_for_ghosts coluna em sys.dm_hadr_database_replica_states.

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

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

Verifique as colunas aborted_version_cleaner_start_time e aborted_version_cleaner_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 for muito menor do que oldest_active_transaction_ide o valor current_abort_transaction_count for grande, é provável que haja uma antiga transação abortada impedindo a limpeza de PVS.

Para resolver o atraso de limpeza do sistema PVS devido a um grande número de transações anuladas, siga estas etapas:

  • 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, confira Configuração do servidor: quantidade de threads do limpador de ADR.
  • Se possível, interrompa o trabalho em andamento para permitir que o limpador de versão progrida.
  • Examine o aplicativo para identificar e resolver 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.
  • Otimize a carga de trabalho para reduzir bloqueios incompatíveis com os bloqueios no nível do objeto ou no nível de partição IX exigidos pelo limpador de PVS. Para obter mais informações, consulte Compatibilidade de bloqueio.
  • Caso esteja usando o SQL Server, desabilite a ADR como uma etapa somente de emergência para controlar o tamanho do PVS. Confira Desabilitar ADR.
  • Se estiver usando o SQL Server e se a limpeza da transação anulada não tiver sido concluída com êxito recentemente, verifique o log de erros em busca de mensagens que relatem 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.

Controlar o tamanho do PVS

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

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

Conteúdo relacionado