Pular para o conteúdo principal

Índice de DocumentUsers (versão 2.21.0)

Esta página explica a otimização da listagem de documentos por usuário (SIG-780), introduzida na versão 2.21.0, e o que acontece quando uma instância atualiza de uma versão anterior à 2.21.0 para a 2.21.0 ou superior, incluindo a 3.x. Instâncias que já estão na 2.21.0 ou superior já passaram por esse processo e não são afetadas.

aviso

Em tabelas DocumentUsers muito grandes, o processo gera um período de maior lentidão e maior consumo de banco de dados depois da atualização. É fortemente recomendado fazer a atualização em um horário de pouco uso. A aplicação continua funcionando durante o processo.

O que essa otimização melhora​

A listagem de documentos de um usuário precisa filtrar e ordenar por situação e data de atualização do documento. Para evitar um JOIN com a tabela Documents a cada listagem, o Signer copia essas duas informações para a tabela DocumentUsers (colunas DocumentStatusCode e DocumentUpdateDateUtc) e cria um índice de cobertura sobre elas:

IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc
  chaves:   UserId, DocumentStatusCode, DocumentUpdateDateUtc (decrescente)
  includes: DocumentId, IsPending, SignerId, ApproverId, SignRuleId, ApproverRuleId

Enquanto o preenchimento e o índice não terminam, a listagem de documentos fica mais lenta, e documentos anteriores à atualização podem aparecer fora de ordem ou não aparecer na listagem por usuário.

Como o processo automático funciona​

O processo tem três etapas, e só a primeira acontece durante a migração:

  1. Migração (DenormalizeDocumentUserWithTrigger). Executada na primeira inicialização. Adiciona as duas colunas (vazias) em DocumentUsers, remove o índice antigo IX_DocumentUsers_UserId e cria o trigger TR_Documents_SyncDocumentUsers em Documents, que mantém as colunas sincronizadas a partir daí. É rápida: não preenche os dados existentes e não cria o índice novo, para não bloquear a atualização.
  2. Enfileiramento na inicialização. O DocumentUserBackfillStarter verifica a configuração DocumentUserDenormalizationCompleted e a existência do índice. Se uma das duas condições não for atendida, enfileira no Hangfire o DocumentUserDenormalizationJob (fila Low, até 3 tentativas). Isso se repete a cada inicialização enquanto o processo não estiver completo.
  3. Job DocumentUserDenormalizationJob. Executa, nesta ordem:
    1. Preenchimento (backfill) das colunas das linhas existentes, em lotes percorrendo a chave Id (tamanho do lote em DocumentUserDenormalizationBatchSize, padrão 10000; timeout de cada lote em DocumentUserDenormalizationBackfillCommandTimeoutSeconds, padrão 300). A cada 20 lotes o job grava o ponto de progresso (DocumentUserDenormalizationBackfillCursor), então uma nova tentativa continua de onde parou.
    2. Marca a conclusão, gravando DocumentUserDenormalizationCompleted = True na tabela Settings.
    3. Cria o índice IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc, com timeout de DocumentUserDenormalizationIndexCommandTimeoutSeconds (padrão 7200). A criação é ONLINE (não bloqueia a tabela) no Azure SQL Database, SQL Managed Instance e SQL Server Enterprise ou Developer, e OFFLINE nas demais edições. O índice só é criado depois do preenchimento, para que as duas operações não disputem a tabela.
perigo

Se a criação ONLINE falhar por qualquer erro de SQL, inclusive timeout, o job tenta criar o índice de forma OFFLINE, o que bloqueia a tabela DocumentUsers durante toda a criação. Em tabelas grandes, evite esse cenário aumentando o timeout antes da atualização ou criando o índice manualmente (veja abaixo).

Como acompanhar​

  • No painel do Hangfire (/hangfire), acompanhe o DocumentUserDenormalizationJob. Quando termina, ele aparece como concluído.
  • Nos logs da aplicação, procure por Backfill DocumentUser denormalization, Creating IX_DocumentUsers_... e created successfully.
  • No banco de dados:
-- Preenchimento concluído? (deve retornar 'True')
SELECT [Value] FROM Settings
WHERE [Key] = 'DocumentUserDenormalizationCompleted' AND AgentId IS NULL AND SubscriptionId IS NULL;

-- Linhas ainda sem preenchimento (deve retornar 0)
SELECT COUNT_BIG(*) FROM DocumentUsers WHERE DocumentStatusCode IS NULL OR DocumentUpdateDateUtc IS NULL;

-- Índice criado?
SELECT name, is_disabled, fill_factor FROM sys.indexes
WHERE name = 'IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc' AND object_id = OBJECT_ID('DocumentUsers');

O que fazer se o processo automático falhar​

O job falhou se ele esgotou as 3 tentativas no Hangfire, ou se os logs mostram timeout ou deadlock (erro SQL 1205). Siga os passos na ordem, e pare assim que o processo concluir.

1. Ajustar as configurações e reiniciar​

Se a falha foi timeout, aumente as configurações abaixo (veja Configurações General) e reinicie a aplicação. O job é enfileirado novamente na inicialização e, se o preenchimento estava em andamento, continua do último ponto gravado.

  • DocumentUserDenormalizationBatchSize: reduza para diminuir o tempo de bloqueio de cada lote em instâncias com tráfego ativo.
  • DocumentUserDenormalizationBackfillCommandTimeoutSeconds: timeout de cada lote do preenchimento.
  • DocumentUserDenormalizationIndexCommandTimeoutSeconds: timeout da criação do índice.

2. Preencher manualmente as colunas​

Se o preenchimento não concluir, execute o script abaixo no banco de dados. Ele percorre a tabela pela chave Id (clustered) em um único passo progressivo, em lotes, e mostra o progresso. Pode ser interrompido e reexecutado sem prejuízo.

SET NOCOUNT ON;

DECLARE @last uniqueidentifier = '00000000-0000-0000-0000-000000000000';
DECLARE @step int = 5000, @rows int = 1;
DECLARE @totalDone bigint = 0, @batch int = 0;
DECLARE @t0 datetime2 = SYSUTCDATETIME();
DECLARE @msg nvarchar(200);

WHILE @rows > 0 BEGIN
  ;WITH batch AS (
    SELECT TOP (@step) Id
    FROM DocumentUsers
    WHERE Id > @last
    ORDER BY Id
  )
  UPDATE du
  SET du.DocumentStatusCode    = d.StatusCode,
      du.DocumentUpdateDateUtc = d.UpdateDateUtc
  FROM DocumentUsers du WITH (ROWLOCK)
  INNER JOIN batch     b ON b.Id = du.Id
  INNER JOIN Documents d ON d.Id = du.DocumentId
  WHERE du.DocumentStatusCode IS NULL OR du.DocumentUpdateDateUtc IS NULL;

  DECLARE @updated int = @@ROWCOUNT;
  SET @totalDone += @updated;
  SET @batch += 1;

  SELECT @last = MAX(Id), @rows = COUNT(*)
  FROM (SELECT TOP (@step) Id FROM DocumentUsers WHERE Id > @last ORDER BY Id) x;

  SET @msg = CONCAT('Lote ', @batch, ' | atualizadas ', @updated, ' | total ', @totalDone,
                    ' | ', DATEDIFF(second, @t0, SYSUTCDATETIME()), 's | last=', CONVERT(char(36), @last));
  SET @msg = REPLACE(@msg, '%', '%%');
  RAISERROR(@msg, 0, 1) WITH NOWAIT;
END

RAISERROR('Preenchimento concluido.', 0, 1) WITH NOWAIT;

Depois, confirme que a contagem de linhas sem preenchimento é 0 (consulta acima) e grave a conclusão, para que o job não repita o preenchimento:

IF EXISTS (SELECT 1 FROM Settings WHERE [Key] = 'DocumentUserDenormalizationCompleted' AND AgentId IS NULL AND SubscriptionId IS NULL)
  UPDATE Settings SET [Value] = 'True'
  WHERE [Key] = 'DocumentUserDenormalizationCompleted' AND AgentId IS NULL AND SubscriptionId IS NULL;
ELSE
  INSERT INTO Settings (Id, [Key], [Value], AgentId, SubscriptionId) VALUES (NEWID(), 'DocumentUserDenormalizationCompleted', 'True', NULL, NULL);

3. Criar o índice manualmente​

Só depois de confirmar que o preenchimento terminou. Faça isso em uma janela de baixo tráfego e em uma única sessão: duas criações ONLINE simultâneas na mesma tabela entram em deadlock na troca final de locks, e o IF NOT EXISTS não protege contra execução concorrente. Em edições sem suporte a ONLINE (SQL Server Standard e Express), use ONLINE = OFF, que bloqueia a tabela durante a criação.

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc' AND object_id = OBJECT_ID('DocumentUsers'))
CREATE INDEX [IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc] ON [DocumentUsers] ([UserId] ASC, [DocumentStatusCode] ASC, [DocumentUpdateDateUtc] DESC)
INCLUDE ([DocumentId], [IsPending], [SignerId], [ApproverId], [SignRuleId], [ApproverRuleId])
WITH (ONLINE = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON);

Cuidados:

  • Deadlock. O build ONLINE disputa locks com as escritas da aplicação e pode causar deadlock (erro 1205); a vítima pode ser uma transação da aplicação em vez do build. Depois da criação, verifique nos logs da aplicação se houve erros 1205 no período.
  • Azure SQL Database. A criação de índice é sempre registrada integralmente no log de transações, então o tempo depende do limite de log do nível de serviço. Níveis baixos, como o S7 de 800 DTU, sofrem throttle (espera LOG_RATE_GOVERNOR; a espera CXCONSUMER é benigna). Em uma instância com cerca de 129 milhões de linhas em DocumentUsers no S7, a criação levou muito tempo e uma das tentativas terminou em deadlock. Se puder, escale o nível de serviço antes de iniciar o build, pois trocar o nível no meio derruba a criação em andamento.
  • Criação retomável (opcional). No SQL Server 2019 ou superior e no Azure SQL, adicione RESUMABLE = ON ao WITH para que uma interrupção não descarte o progresso (consulte sys.index_resumable_operations e use ALTER INDEX ... RESUME ou ABORT). RESUMABLE = ON não pode ser combinado com SORT_IN_TEMPDB = ON; remova essa opção.

4. Confirmar e reiniciar​

Execute as consultas da seção Como acompanhar: a conclusão deve estar True, nenhuma linha pendente e o índice presente. Reinicie a aplicação. Com a conclusão gravada e o índice criado, o DocumentUserBackfillStarter não enfileira mais o job.

Ver em Markdown