servidor sql: atualizando campos em uma tabela enorme em pequenos pedaços: como obter progresso/status?

Eu não estava ciente dessa questão quando respondi à pergunta relacionada ( As transações explícitas são necessárias neste loop while? ), Mas, para fins de integridade, abordarei esse problema aqui, pois não fazia parte da minha sugestão nessa resposta vinculada .

Como estou sugerindo agendar isso por meio de um trabalho do SQL Agent (afinal, são 100 milhões de linhas), não acho que qualquer forma de enviar mensagens de status ao cliente (ou seja, SSMS) seja ideal (embora, se isso for sempre uma necessidade de outros projetos, então concordo com Vladimir que usar RAISERROR('', 10, 1) WITH NOWAIT;é o caminho a percorrer).

Nesse caso específico, eu criaria uma tabela de status que pode ser atualizada a cada loop com o número de linhas atualizadas até o momento. E não custa nada jogar no tempo atual para ter uma batida de coração no processo.

Dado que você deseja cancelar e reiniciar o processo,Estou cansado de envolver o UPDATE da tabela principal com o UPDATE da tabela de status em uma transação explícita. No entanto, se você sentir que a tabela de status está fora de sincronia devido ao cancelamento, é fácil atualizar com o valor atual simplesmente atualizando-o manualmente com o arquivo COUNT(*) FROM [huge-table] WHERE deleted IS NOT NULL AND deletedDate IS NOT NULL.e há duas tabelas para atualizar (ou seja, a tabela principal e a tabela de status), devemos usar uma transação explícita para manter essas duas tabelas em sincronia, mas não queremos correr o risco de ter uma transação órfã se você cancelar o processo em um ponto depois de ter iniciado a transação, mas não a confirmou. Isso deve ser seguro desde que você não interrompa o trabalho do SQL Agent.

Como você pode interromper o processo sem, bem, interrompê-lo? Pedindo para parar :-). Sim. Ao enviar um "sinal" ao processo (semelhante ao kill -3Unix), você pode solicitar que ele pare no próximo momento conveniente (ou seja, quando não houver nenhuma transação ativa!) e faça com que ele se limpe de maneira agradável e organizada.

Como você pode se comunicar com o processo em execução em outra sessão? Usando o mesmo mecanismo que criamos para comunicar seu status atual para você: a tabela de status. Basta adicionar uma coluna que o processo verificará no início de cada loop para saber se deve prosseguir ou abortar. E como a intenção é agendar isso como um trabalho do SQL Agent (executado a cada 10 ou 20 minutos), também devemos verificar logo no início, pois não adianta preencher uma tabela temporária com 1 milhão de linhas se o processo estiver apenas acontecendo para sair um momento depois e não usar nenhum desses dados.

DECLARE @BatchRows INT = 1000000,
        @UpdateRows INT = 4995;

IF (OBJECT_ID(N'dbo.HugeTable_TempStatus') IS NULL)
BEGIN
  CREATE TABLE dbo.HugeTable_TempStatus
  (
    RowsUpdated INT NOT NULL, -- updated by the process
    LastUpdatedOn DATETIME NOT NULL, -- updated by the process
    PauseProcess BIT NOT NULL -- read by the process
  );

  INSERT INTO dbo.HugeTable_TempStatus (RowsUpdated, LastUpdatedOn, PauseProcess)
  VALUES (0, GETDATE(), 0);
END;

-- First check to see if we should run. If no, don't waste time filling temp table
IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
BEGIN
  PRINT 'Process is paused. No need to start.';
  RETURN;
END;

CREATE TABLE #FullSet (KeyField1 DataType1, KeyField2 DataType2);
CREATE TABLE #CurrentSet (KeyField1 DataType1, KeyField2 DataType2);

INSERT INTO #FullSet (KeyField1, KeyField2)
  SELECT TOP (@BatchRows) ht.KeyField1, ht.KeyField2
  FROM   dbo.HugeTable ht
  WHERE  ht.deleted IS NULL
  OR     ht.deletedDate IS NULL

WHILE (1 = 1)
BEGIN
  -- Check if process is paused. If yes, just exit cleanly.
  IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
  BEGIN
    PRINT 'Process is paused. Exiting.';
    BREAK;
  END;

  -- grab a set of rows to update
  DELETE TOP (@UpdateRows)
  FROM   #FullSet
  OUTPUT Deleted.KeyField1, Deleted.KeyField2
  INTO   #CurrentSet (KeyField1, KeyField2);

  IF (@@ROWCOUNT = 0)
  BEGIN
    RAISERROR(N'All rows have been updated!!', 16, 1);
    BREAK;
  END;

  BEGIN TRY
    BEGIN TRAN;

    -- do the update of the main table
    UPDATE ht
    SET    ht.deleted = 0,
           ht.deletedDate = '2000-01-01'
    FROM   dbo.HugeTable ht
    INNER JOIN #CurrentSet cs
            ON cs.KeyField1 = ht.KeyField1
           AND cs.KeyField2 = ht.KeyField2;

    -- update the current status
    UPDATE ts
    SET    ts.RowsUpdated += @@ROWCOUNT,
           ts.LastUpdatedOn = GETDATE()
    FROM   dbo.HugeTable_TempStatus ts;

    COMMIT TRAN;
  END TRY
  BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
      ROLLBACK TRAN;
    END;

    THROW; -- raise the error and terminate the process
  END CATCH;

  -- clear out rows to update for next iteration
  TRUNCATE TABLE #CurrentSet;

  WAITFOR DELAY '00:00:01'; -- 1 second delay for some breathing room
END;

-- clean up temp tables when testing
-- DROP TABLE #FullSet; 
-- DROP TABLE #CurrentSet; 

Você pode verificar o status a qualquer momento usando a seguinte consulta:

SELECT sp.[rows] AS [TotalRowsInTable],
       ts.RowsUpdated,
       (sp.[rows] - ts.RowsUpdated) AS [RowsRemaining],
       ts.LastUpdatedOn
FROM sys.partitions sp
CROSS JOIN dbo.HugeTable_TempStatus ts
WHERE  sp.[object_id] = OBJECT_ID(N'ResizeTest')
AND    sp.[index_id] < 2;

Deseja pausar o processo, seja em um trabalho do SQL Agent ou até mesmo no SSMS no computador de outra pessoa? Apenas corra:

UPDATE ht
SET    ht.PauseProcess = 1
FROM   dbo.HugeTable_TempStatus ts;

Quer que o processo seja capaz de começar de novo? Apenas corra:

UPDATE ht
SET    ht.PauseProcess = 0
FROM   dbo.HugeTable_TempStatus ts;

ATUALIZAR:

Aqui estão algumas coisas adicionais para tentar que podem melhorar o desempenho desta operação. Nenhum é garantido para ajudar, mas provavelmente vale a pena testar. E com 100 milhões de linhas para atualizar, você tem bastante tempo/oportunidade para testar algumas variações ;-).

1. Adicione TOP (@UpdateRows)à consulta UPDATE para que a linha superior se pareça com:
   UPDATE TOP (@UpdateRows) ht
   Às vezes, ajuda o otimizador a saber quantas linhas no máximo serão afetadas, para que não perca tempo procurando por mais.

2. Adicione uma PRIMARY KEY à #CurrentSettabela temporária. A ideia aqui é ajudar o otimizador com o JOIN para a tabela de 100 milhões de linhas.

   E só para deixar claro para não ser ambíguo, não deve haver nenhuma razão para adicionar um PK à #FullSettabela temporária, pois é apenas uma tabela de fila simples onde a ordem é irrelevante.

3. Em alguns casos, ajuda adicionar um índice filtrado para auxiliar o SELECTque alimenta a #FullSettabela temporária. Aqui estão algumas considerações relacionadas à adição de tal índice:
   1. A condição WHERE deve corresponder à condição WHERE da sua consulta, portantoWHERE deleted is null or deletedDate is null
   2. No início do processo, a maioria das linhas corresponderá à sua condição WHERE, portanto, um índice não é tão útil. Você pode querer esperar até algo em torno da marca de 50% antes de adicionar isso. Claro, o quanto isso ajuda e quando é melhor adicionar o índice varia devido a vários fatores, então é um pouco de tentativa e erro.
   3. Você pode ter que ATUALIZAR ESTATÍSTICAS manualmente e/ou RECONSTRUIR o índice para mantê-lo atualizado, pois os dados básicos mudam com bastante frequência
   4. Lembre-se de que o índice, embora ajude o SELECT, prejudicará o UPDATE, pois é outro objeto que deve ser atualizado durante essa operação, portanto, mais I/O. Isso funciona tanto usando um índice filtrado (que diminui à medida que você atualiza as linhas, pois menos linhas correspondem ao filtro) quanto esperando um pouco para adicionar o índice (se não for muito útil no início, não há razão para incorrer a E/S adicional).

Respondendo a segunda parte: como imprimir alguma saída durante o loop.

Eu tenho alguns procedimentos de manutenção de longa duração que o administrador do sistema às vezes precisa executar.

Eu os executo do SSMS e também notei que a PRINTinstrução é mostrada no SSMS somente após o término de todo o procedimento.

Então, estou usando RAISERRORcom baixa severidade:

DECLARE @VarTemp nvarchar(32);
SET @VarTemp = CONVERT(nvarchar(32), GETDATE(), 121);
RAISERROR (N'Your message. Current time is %s.', 0, 1, @VarTemp) WITH NOWAIT;

Estou usando SQL Server 2008 Standard e SSMS 2012 (11.0.3128.0). Aqui está um exemplo de trabalho completo para executar no SSMS:

DECLARE @VarCount int = 0;
DECLARE @VarTemp nvarchar(32);

WHILE @VarCount < 3
BEGIN
    SET @VarTemp = CONVERT(nvarchar(32), GETDATE(), 121);
    --RAISERROR (N'Your message. Current time is %s.', 0, 1, @VarTemp) WITH NOWAIT;
    --PRINT @VarTemp;

    WAITFOR DELAY '00:00:02';
    SET @VarCount = @VarCount + 1;
END

Quando eu comento RAISERRORe deixo apenas PRINTas mensagens na guia Mensagens no SSMS aparecem somente após o término de todo o lote, após 6 segundos.

Quando eu comento PRINTe uso RAISERRORas mensagens na guia Mensagens no SSMS aparecem sem esperar 6 segundos, mas conforme o loop avança.

Curiosamente, quando uso ambos RAISERRORe PRINT, vejo ambas as mensagens. Primeiro vem a mensagem do primeiro RAISERROR, depois atrasa por 2 segundos, depois primeiro PRINTe segundo RAISERRORe assim por diante.

Em outros casos, uso uma tabela dedicada separada loge simplesmente insiro uma linha na tabela com algumas informações que descrevem o estado atual e o carimbo de data/hora do processo de execução longa.

Enquanto o longo processo é executado, eu periodicamente SELECTda logmesa para ver o que está acontecendo.

Isso obviamente tem certa sobrecarga, mas deixa um log (ou histórico de logs) que posso examinar em meu próprio ritmo mais tarde.

Você pode monitorá-lo de outra conexão com algo como:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT COUNT(*) FROM [huge-table] WHERE deleted IS NULL OR deletedDate IS NULL 

para ver quanto falta fazer. Isso pode ser útil se um aplicativo estiver chamando o processo, em vez de executá-lo manualmente no SSMS ou similar, e precisar mostrar o progresso: execute o processo principal de forma assíncrona (ou em outro thread) e, em seguida, faça um loop chamando o "quanto resta " verifique de vez em quando até que a chamada assíncrona (ou thread) seja concluída.

Definir o nível de isolamento o mais frouxo possível significa que ele deve retornar em um tempo razoável sem ficar preso atrás da transação principal devido a problemas de bloqueio. Isso pode significar que o valor retornado é um pouco impreciso, é claro, mas como um simples medidor de progresso, isso não deve importar.