Solucionar problemas de limpeza automática do controle de alterações

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Este artigo fornece maneiras de solucionar problemas comuns observados na limpeza automática do controle de alterações.

Sintomas

Geralmente, se a limpeza automática não estiver funcionando conforme o esperado, você poderá perceber um ou mais dos seguintes sintomas:

  • Alto consumo de armazenamento por uma ou mais tabelas laterais do controle de alterações ou pela tabela do sistema syscommittab.
  • As tabelas laterais (tabelas internas com o nome começando com um prefixo change_tracking (por exemplo, change_tracking_12345), syscommittab ou ambos, mostram um número significativo de linhas que estão fora do período de retenção configurado.
  • A tabela dbo.MSChange_tracking_history tem entradas com erros de limpeza específicos.
  • O desempenho de CHANGETABLE degradou-se ao longo do tempo.
  • A limpeza automática ou manual informa um alto uso da CPU.

Depuração e mitigação

Para identificar a causa raiz de um problema com a limpeza automática do controle de alterações, use as etapas a seguir para depurar e mitigar o problema.

Status da limpeza automática

Verifique se a limpeza automática está em execução. Para isso, consulte a tabela de histórico de limpeza no mesmo banco de dados. Se a limpeza estiver em execução, a tabela terá entradas com as horas de início e término da limpeza. Se a limpeza não estiver em execução, a tabela estará vazia ou terá entradas obsoletas. Se a tabela de histórico tiver entradas com a tag cleanup errors na coluna comments, então a limpeza está falhando devido a erros de limpeza no nível da tabela.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

A limpeza automática é executada periodicamente com um intervalo padrão de 30 minutos. Se a tabela de histórico não existir, é provável que a limpeza automática nunca foi executada. Caso contrário, verifique os valores das colunas start_time e end_time. Se as entradas mais recentes não forem recentes, ou seja, tiverem horas ou dias, a limpeza automática pode não estar em execução. Se for esse o caso, use as etapas a seguir para solucionar problemas.

1. A limpeza está desligada

Verifique se a limpeza automática está ligada para o banco de dados. Se não estiver, ligue-a e aguarde pelo menos 30 minutos antes consultar a tabela de histórico em busca de novas entradas. Monitore o progresso na tabela de histórico daí em diante.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

Um valor diferente de zero em is_auto_cleanup_on indica que a limpeza automática está habilitada. O valor do período de retenção controla a duração da retenção dos metadados do controle de alterações no sistema. O valor padrão para o período de retenção do controle de alterações é de dois dias.

Para habilitar ou desabilitar o controle de alterações, consulte Habilitar e desabilitar o controle de alterações (SQL Server).

2. A limpeza está ligada, mas não está em execução

Se a limpeza automática estiver ativada, o thread da limpeza automática provavelmente foi interrompido devido a erros inesperados. Atualmente, não é viável reiniciar o thread da limpeza automática. Você deve iniciar um failover para um servidor secundário (ou reiniciar o servidor na ausência de um secundário) e confirmar se a configuração da limpeza automática está habilitada para o banco de dados.

A limpeza automática é executada, mas não está progredindo

Se uma ou mais tabelas laterais mostrarem um consumo de armazenamento significativo ou contiverem um grande número de registros além da retenção configurada, siga as etapas nesta seção, que descrevem as medidas para uma única tabela lateral. As mesmas etapas podem ser repetidas para mais tabelas, se necessário.

1. Avalie a lista de pendências da limpeza automática

Identifique tabelas laterais que têm uma grande lista de pendências de registros expirados, que precisam que seja executada mitigação. Execute as consultas a seguir para identificar as tabelas laterais com grande número de registros expirados. Lembre-se de substituir os valores nos scripts de exemplo conforme mostrado.

  1. Obtenha a versão de limpeza inválida:

    SELECT * FROM sys.change_tracking_tables;

    O valor de cleanup_version das linhas retornadas representa a versão de limpeza inválida.

  2. Execute a seguinte consulta dinâmica Transact-SQL (T-SQL), que gera a consulta para obter o número de linhas expiradas de tabelas laterais. Substitua o valor de <invalid_version> na consulta pelo valor obtido na etapa anterior.

    SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) FROM [sys].' + QUOTENAME(name)
    + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
FROM sys.internal_tables
WHERE internal_type = 209;

  3. Copie o conjunto de resultados da consulta anterior e remova a palavra-chave UNION da última linha. Se você executar a consulta T-SQL gerada por meio de uma conexão de administrador (DAC) dedicada, a consulta fornecerá os números de linhas expiradas de todas as tabelas laterais. Dependendo do tamanho da tabela sys.syscommittab e do número de tabelas laterais, essa consulta pode levar muito tempo para ser concluída.

    Importante

    Esta etapa é necessária para avançar com as etapas de mitigação. Se a consulta anterior não for executada, identifique o número de linhas expiradas para as tabelas laterais individuais usando as consultas fornecidas a seguir.

Execute as seguintes etapas de mitigação para as tabelas laterais, tendo a ordem decrescente de número de linhas expiradas, até que os números de linhas expiradas cheguem a um estado gerenciável para que a limpeza automática se atualize.

Depois que você identificar as tabelas laterais com grande número de registros expirados, reúna informações sobre a latência das instruções de exclusão da tabela lateral e a taxa de exclusão por segundo nas últimas horas. Em seguida, estime o tempo necessário para limpar a tabela lateral considerando a contagem de linhas obsoletas e a latência de exclusão.

Use o trecho de código T-SQL a seguir substituindo modelos de parâmetro pelos valores apropriados.

  • Consulte a taxa de limpeza por segundo:

    SELECT
    table_name,
    rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
    cleanup_version
FROM dbo.MSChange_tracking_history
WHERE table_name = '<table_name>'
ORDER BY end_time DESC;

    Também é possível usar granularidade de minuto ou hora para a função DATEDIFF.

  • Procure a contagem de linhas obsoletas na tabela lateral. Essa consulta ajuda você a procurar o número de linhas pendentes para serem limpas.

    O <internal_table_name> e <cleanup_version> para a tabela do usuário estão na saída retornada na seção anterior. Usando essas informações, execute o seguinte código T-SQL por meio de uma conexão de administrador dedicada (DAC):

    SELECT '<internal_table_name>',
    COUNT(*)
FROM sys.<internal_table_name>
WHERE sys_change_xdes_id IN (
        SELECT xdes_id
        FROM sys.syscommittab ssct
        WHERE ssct.commit_ts <= <cleanup_version>
);

    Essa consulta pode levar algum tempo para ser concluída. Se a consulta atingir o tempo limite, calcule as linhas obsoletas procurando a diferença entre linhas totais e linhas ativas, ou seja, linhas a serem limpas.

  • Localize o número total de linhas na tabela lateral executando a seguinte consulta:

    SELECT sum(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
GROUP BY partition_id;

  • Localize o número de linhas ativas na tabela lateral executando a seguinte consulta:

    SELECT '<internal_table_name>', COUNT(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);

    Você pode calcular o tempo estimado para limpar a tabela usando a taxa de limpeza e a contagem de linhas obsoletas. Considere a seguinte fórmula:

    Tempo para limpar em minutos = (contagem de linhas obsoletas) / (taxa de limpeza em minutos)

    Se o tempo para concluir a limpeza da tabela for aceitável, monitore o progresso e deixe a limpeza automática continuar seu trabalho. Caso contrário, prossiga com as próximas etapas para analisar mais.

2. Verifique os conflitos de bloqueio de tabela

Determine se a limpeza não está progredindo devido a conflitos de escalonamento de bloqueio de tabela, que impedem a limpeza de adquirir bloqueios na tabela lateral para excluir linhas.

Para confirmar um conflito de bloqueio, execute o código T-SQL a seguir. Essa consulta efetua o fetch de registros da tabela problemática para determinar se há várias entradas indicando conflitos de bloqueio. Alguns conflitos esporádicos espalhados por um período não devem se qualificar para as etapas de mitigação do processo. Os conflitos devem ser recorrentes.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

Se a tabela de histórico tiver várias entradas nas colunas comments com o valor Cleanup error: Lock request time out period exceeded, é uma indicação clara de que várias tentativas de limpeza falharam devido a conflitos de bloqueio ou tempos limite de bloqueio em sucessão. Considere as seguintes correções:

  • Desabilite e habilite o controle de alterações na tabela problemática. Isso faz com que todos os metadados de controle mantidos para a tabela sejam limpos. Os dados da tabela permanecem intactos. Esta é a medida mais rápida.

  • Se a opção anterior não for possível, prossiga para executar a limpeza manual na tabela ativando o sinalizador de rastreamento 8284 da seguinte maneira:

    DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';

3. Verifique outras causas

Outra possível causa do atraso na limpeza é a lentidão das instruções de exclusão. Para determinar se esse é o caso, verifique o valor de hardened_cleanup_version. Esse valor pode ser recuperado por meio de uma conexão de administrador dedicada (DAC) com o banco de dados em consideração.

Localize a versão de limpeza reforçada executando a seguinte consulta:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

Localize a versão de limpeza executando a seguinte consulta:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

Se os valores hardened_cleanup_version e cleanup_version forem iguais, ignore esta seção e prossiga para a próxima.

Se os dois valores forem diferentes, isso significa que uma ou mais tabelas laterais encontraram erros. A mitigação mais rápida é desabilitar e habilitar o controle de alterações na tabela problemática. Isso faz com que todos os metadados de controle mantidos para a tabela sejam limpos. Os dados na tabela permanecem intactos.

Se a opção anterior não for possível, execute a limpeza manual na tabela.

Solucionar problemas do syscommittab

Esta seção aborda as etapas para depurar e mitigar problemas com a tabela do sistema syscommittab, se ela usar muito espaço de armazenamento ou se tiver uma grande lista de pendências de linhas obsoletas.

A limpeza da tabela do sistema syscommittab depende da limpeza da tabela lateral. syscommittab poderá ser limpa somente depois que todas as tabelas laterais forem limpas. Verifique se todas as etapas na seção Limpeza automática são executadas, mas não estão progredindo são executadas.

Para invocar explicitamente a limpeza syscommittab, use o procedimento armazenado sys.sp_flush_commit_table_on_demand.

Observação

O procedimento armazenado sys.sp_flush_commit_table_on_demand pode levar tempo se estiver excluindo uma grande lista de pendências de linhas.

Conforme mostrado na seção de exemplo do artigo sys.sp_flush_commit_table_on_demand, esse procedimento armazenado retorna o valor de e o número de safe_cleanup_version() e o número de linhas excluídas. Se o valor retornado parecer ser 0, e se o isolamento de instantâneo estiver ativado, a limpeza pode não excluir nada de syscommittab.

Se o período de retenção for maior que um dia, deve ser seguro executar novamente o procedimento armazenado sys.sp_flush_commit_table_on_demand depois de habilitar o Sinalizador de Rastreamento 8239 globalmente. Usar esse sinalizador de rastreamento quando o isolamento de instantâneo está desativado é sempre seguro, mas, em alguns casos, pode não ser necessário.

Alta utilização da CPU durante a limpeza

O problema descrito nesta seção pode ser visto em versões mais antigas do SQL Server. Se houver um grande número de tabelas com controle de alterações em um banco de dados e a limpeza automática ou manual causar alta utilização da CPU. Esse problema também pode ser causado devido à tabela de histórico, que foi mencionada brevemente em seções anteriores.

Use o seguinte código T-SQL para verificar o número de linhas na tabela de histórico:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Se o número de linhas for suficientemente grande, tente adicionar o índice a seguir se ele estiver ausente. Adicione o seguinte código T-SQL ao índice:

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

Executar a limpeza com mais de 30 minutos

Tabelas específicas podem sofrer uma alta taxa de alterações, e você pode descobrir que o trabalho de limpeza automática não consegue limpar as tabelas laterais e syscommittab dentro do intervalo de 30 minutos. Se isso ocorrer, você pode executar um trabalho de limpeza manual com maior frequência para facilitar o processo.

Para o SQL Server e a Instância Gerenciada de SQL do Azure, crie um trabalho em segundo plano usando sp_flush_CT_internal_table_on_demand com um intervalo interno menor que o padrão de 30 minutos. Para o Banco de Dados SQL do Azure, os Aplicativos Lógicos do Azure podem ser usados para agendar esses trabalhos.

A seguir, um exemplo de código T-SQL pode ser usado para criar um trabalho para ajudar a limpar as tabelas laterais do Controle de Alterações:

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

Conteúdo relacionado