Using CHECKPOINT vs GO

Question

Typically, when one of the developers or data analysts needs to perform a very large update or deletion of data (where a truncate or truncate/insert wouldn't make sense because the data set to keep is too large) I recommend to them to do something like the following:

-- Delete 1 million rows 1 thousand at a time
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
GO 1000

The result of this on databases that are in full recovery mode is that 1) the wait allows other transactions to process if needed and 2) when the log backup runs it is able to mark the operations already completed as dirty in the log file so it can re-use the space and prevent the log from growing too rapidly.

Rather than doing this, I'd like to know if it's possible to accomplish the same thing using checkpoints. Would this statement effectively result in the same situation from happening?

-- Delete 1 million rows 1 thousand at a time
WHILE EXISTS ( SELECT 1 FROM TableA WHERE <condition> )
BEGIN
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
CHECKPOINT
END

Again, these are databases in full recovery mode.

score 2 · Accepted Answer · answered Mar 04 '17 at 04:22

The most important thing when doing a large update or delete is to avoid the transaction log growing out of control. To allow log reuse & avoid bloating to Tlog (Transaction log truncation)

In full recovery mode - take log backups
In Simple recovery mode - do checkpoints.

Since you are using Full recovery, your delete logic should be

begin
 begin tran
   -- delete using batches 
   -- carefully choose batch size. SQL Server will do a lock escalation 
   -- for batch greater than 5000. (https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx)
 commit tran / rollback tran 
  -- since you are using Full recovery, take log backups
  -- with CHECKPOINT SQL Server still cannot reuse those portions of the log until you perform a log backup.
end

I use waitfor delay to esp. avoid prolonged blocking.

Refer to this gold mine blog post - Break large delete operations into chunks by Aaron Bertrand. Also, Take Care When Scripting Batches is very useful blog post.

score 1 · Answer 2 · answered Mar 04 '17 at 08:37

The two are equivalent and you're confusing batching with log truncation.

The important thing is that the code runs in batches so that locks get released and other sessions can jump in and read the table (and also so the log file can clear when the database is in in the simple recovery model).

However - in your first example the WAITFOR likely isn't necessary - all of the sessions are queued up waiting and will likely get in between each run.

The second is equivalent except it tells SIMPLE mode databases that the log can be cleared, however, this is likely to happen every few seconds anyway! In FULL recovery model neither is adding much value.

Using CHECKPOINT vs GO

2 Answers2