sql server: updating fields on huge table in small chunks: how to get progress/status?

Question

We have a very large (100million row) table, and we need to update a couple of fields on it.

For log shipping, etc, we also, obviously, want to keep it to bite-size transactions.

• Will the below do the trick?
• And how can we get it to print some output, so we can see progress? (we tried adding a PRINT statement in there, but nothing was output during while loop)

The code is:

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 10000

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END

Answer 1

I was not aware of this question when I answered the related question ( Are explicit transactions needed in this while loop? ), but for the sake of completeness, I will address this issue here as it was not part of my suggestion in that linked answer.

Since I am suggesting to schedule this via a SQL Agent job (it is 100 million rows, after all), I don't think that any form of sending status messages to the client (i.e. SSMS) will be ideal (though if that is ever a need for other projects, then I agree with Vladimir that using RAISERROR('', 10, 1) WITH NOWAIT; is the way to go).

In this particular case, I would create a status table that can be updated per each loop with the number of rows updated thus far. And it doesn't hurt to throw in the current time to have a heart-beat on the process.

Given that you want to be able to cancel and restart the process, I am weary of wrapping the the UPDATE of the main table with the UPDATE of the status table in an explicit transaction. However, if you feel that the status table is ever out of sync due to cancelling, it is easy to refresh with the current value by simply updating it manually with the COUNT(*) FROM [huge-table] WHERE deleted IS NOT NULL AND deletedDate IS NOT NULL. and there are two tables to UPDATE (i.e. the main table and the status table), we should use an explicit transaction to keep those two tables in sync, yet we do not want to risk having an orphaned transaction if you cancel the process at a point after it has started the transaction but has not committed it. This should be safe to do as long as you don't stop the SQL Agent job.

How can you stop the process without, um, well, stopping it? By asking it to stop :-). Yep. By sending the process a "signal" (similar to kill -3 in Unix), you can request that it stop at the next convenient moment (i.e. when there is no active transaction!) and have it clean itself up all nice and tidy-like.

How can you communicate with the running process in another session? By using the same mechanism that we created for it to communicate its current status back to you: the status table. We just need to add a column that the process will check at the beginning of each loop so that it knows whether to proceed or abort. And since the intent is to schedule this as a SQL Agent job (run every 10 or 20 minutes), we should also check at the very beginning since there is no point in filling a temp table with 1 million rows if the process is just going to exit a moment later and not use any of that data.

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; 

You can then check the status at any time using the following query:

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;

Want to pause the process, whether it is running in a SQL Agent job or even in SSMS on someone else's computer? Just run:

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

Want the process to be able to start back up again? Just run:

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

---

UPDATE:

Here are some additional things to try that might improve the performance of this operation. None are guaranteed to help but are probably worth testing out. And with 100 million rows to update, you have plenty of time / opportunity to test some variations ;-).

1. Add TOP (@UpdateRows) to the UPDATE query so that the top line looks like:
   UPDATE TOP (@UpdateRows) ht
   Sometimes it helps the optimizer to know how many rows max will be affected so it doesn't waste time looking for more.

2. Add a PRIMARY KEY to the #CurrentSet temporary table. The idea here is to help the optimizer with the JOIN to the 100 million row table.

   And just to have it stated so as to not be ambiguous, there shouldn't be any reason to add a PK to the #FullSet temporary table as it is just a simple queue table where the order is irrelevant.

3. In some cases it helps to add a Filtered Index to assist the SELECT that feeds into the #FullSet temp table. Here are some considerations related to adding such an index:
   1. The WHERE condition should match the WHERE condition of your query, hence WHERE deleted is null or deletedDate is null
   2. At the beginning of the process, most rows will match your WHERE condition, so an index isn't that helpful. You might want to wait until somewhere around the 50% mark before adding this. Of course, how much it helps and when it is best to add the index vary due to several factors, so it is a bit of trial and error.
   3. You might have to manually UPDATE STATS and/or REBUILD the index to keep it up to date since the base data is changing quite frequently
   4. Be sure to keep in mind that the index, while helping the SELECT, will hurt the UPDATE since it is another object that must be updated during that operation, hence more I/O. This plays into both using a Filtered Index (which shrinks as you update rows since fewer rows match the filter), and waiting a little while to add the index (if it's not going to be super helpful in the beginning, then no reason to incur the additional I/O).

Answer 2

Answering the second part: how to print some output during the loop.

I have few long-running maintenance procedures that sys admin sometimes has to run.

I run them from SSMS and also noticed that PRINT statement is shown in SSMS only after the whole procedure finishes.

So, I'm using RAISERROR with low severity:

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

I'm using SQL Server 2008 Standard and SSMS 2012 (11.0.3128.0). Here is a complete working example to run in 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

When I comment out RAISERROR and leave only PRINT the messages in Messages tab in SSMS appear only after the whole batch is finished, after 6 seconds.

When I comment out PRINT and use RAISERROR the messages in Messages tab in SSMS appear without waiting for 6 seconds, but as loop progresses.

Interestingly, when I use both RAISERROR and PRINT, I see both messages. First comes message from first RAISERROR, then delay for 2 seconds, then first PRINT and second RAISERROR, and so on.

---

In other cases I use a separate dedicated log table and simply insert a row into the table with some information describing the current state and timestamp of the long-running process.

While the long process runs I periodically SELECT from log table to see what is going on.

This obviously has certain overhead, but it leaves a log (or history of logs) that I can examine at my own pace later.

Answer 3

You could monitor it from another connection with something like:

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

to see how much is left to do. This could be useful if an application is calling the process, rather than you running it manually in SSMS or similar, and needs to show progress: run the main process asynchronously (or on another thread) and then loop calling the "how much is left" check every while until the async call (or thread) completes.

Setting the isolation level as lax as possible means that this should return in reasonable time without being stuck behind the main transaction due to locking issues. It could mean the returned value is a little inaccurate of course, but as a simple progress meter this should not matter at all.