How to Delete From Large Tables in Batches Without Growing the Transaction Log

by Greg M Lucas on 29 April 2012

I was working with a colleague a few months back to find an efficient method for deleting the entire contents of a large table without using TRUNCATE or causing excessive log growth. There were reasons why we couldn’t temporarily remove the foreign keys which meant that TRUNCATE TABLE wasn’t an option so we had to try and use DELETE in batches to avoid running out of log space. We tried several options and I suddenly remembered an approach I used to use back in the days of SQL Server v7 to return top ‘n’ rows (before TOP (@N) was supported. I wondered if the same approach could be used to batch up our DELETEs and in the process discovered a way to minimise transaction log growth in the process.

Preparation

For the purposes of this article, I have created two databases BatchDeleteTestA and BatchDeleteTestB both of which are set to SIMPLE RECOVERY (as per most Dev or system test environments). The complete setup script can be found here – you’ll just need to change the database name and possibly the file paths. Basically this script drops and recreates a database with a 1 GB data file and a 200 MB transaction log file. It goes on to create a table to hold the data we’re going to delete plus a couple of tables we’ll use later to monitor t-log growth and any tempdb use. The relevant bits of code are here

CREATE DATABASE BatchDeleteTestB
ON PRIMARY
    ( NAME = 'BatchDeleteTestB_data'
    , FILENAME = 'Z:\temp\BatchDeleteTestB_data.mdf'
    , SIZE = 1 GB
    , FILEGROWTH = 10 %
    , MAXSIZE = UNLIMITED
    )
LOG ON
    ( NAME = 'BatchDeleteTestB_log'
    , FILENAME = 'Z:\temp\BatchDeleteTestB_log.ldf'
    , SIZE = 200 MB
    , FILEGROWTH = 10 %
    , MAXSIZE = UNLIMITED
    )
GO

ALTER DATABASE BatchDeleteTestB SET RECOVERY SIMPLE;
GO

CREATE TABLE [dbo].[SimpleBatchDelete]
(
  Id           int            NOT NULL
, StringValue  nvarchar(2000)  NOT NULL
, CONSTRAINT PK_SimpleBatchDelete PRIMARY KEY CLUSTERED (Id)
)
ON [DEFAULT]

After creating the table, we need to populate it with enough data to be useful to our test. The easiest way to do this was to use
a virtual numbers table wrapped up in an in-line function based on Itzik Ben-Gan’s Virtual Auxiliary Table of Numbers.

CREATE FUNCTION [dbo].[Numbers]
(
  @MaxNum int
)
RETURNS table
AS
RETURN
    (
        WITH L0(c) AS
              (SELECT 1 UNION ALL SELECT 1)
        , L1 (n) AS (SELECT 1 FROM L0 AS A CROSS JOIN L0 AS B)
        , L2 (n) AS (SELECT 1 FROM L1 AS A CROSS JOIN L1 AS B)
        , L3 (n) AS (SELECT 1 FROM L2 AS A CROSS JOIN L2 AS B)
        , L4 (n) AS (SELECT 1 FROM L3 AS A CROSS JOIN L3 AS B)
        , L5 (n) AS (SELECT 1 FROM L4 AS A CROSS JOIN L4 AS B)
        , L6 (n) AS (SELECT 1 FROM L5 AS A CROSS JOIN L5 AS B)
        SELECT
              TOP( CASE WHEN @MaxNum IS NULL THEN 0 ELSE @MaxNum END) ROW_NUMBER() OVER (ORDER BY n) num
         FROM
            L6
    )
GO

The Numbers table (aka Tally table) has been around for years, but if you’ve never heard of this before, it is just a table with a single integer column which contains a sequence of numbers – anything from a few thousand to (potentially) millions. These can have a number of uses but one of the more common uses is splitting strings based on some delimiter using a set-based approach rather than a loop. Adam Machanic (blog | twitter) is a big fan of this concept and at the bottom of this article I’ve added some links to some of the use cases he’s identified.

The one down side to a physical numbers table is that as more and more functions and procedures calls start to access, it can become a bottleneck. This is where Itzik’s solution works so well as the the list of numbers are built in memory; and in the implementation demonstrated here, only the required numbers are returned. Read Itzik’s article above if you want to learn more.

So using our [Numbers] function, we populate SimpleBatchDelete like this…

DECLARE @NumRows int; SET @NumRows = 1234567; 

INSERT SimpleBatchDelete
(
  Id
, StringValue
)
SELECT
      num
    , @StringValue
FROM
    dbo.Numbers(@NumRows)

First Pass

So now we have a table with 1.2M rows in a database that uses the SIMPLE Recovery model and we need to delete all of those rows without using TRUNCATE and without growing the transaction log. This is a simplified version of the first pass at batching up these DELETEs. You can download the full version of this script including the logging here.

DECLARE @RowCount int; SET @RowCount = 1; -- Must be >; 0 to initiate the loop

WHILE @RowCount >; 0
    BEGIN
        DELETE FROM dbo.SimpleBatchDelete
        WHERE Id IN
            (
                SELECT TOP (50000) Id FROM dbo.SimpleBatchDelete
            )

        --! Only continue processing if there's anything left to delete
        SELECT @RowCount = COUNT(*) FROM dbo.SimpleBatchDelete;
    END
GO

In order to compare the t-log usage, we use a really useful DMO (Dynamic Management Object) that allows us access to the SQL server Counters that are also available in Windows. We’ll dump the results from this query into a table before and after running our DELETEs and then compare the results to see how the t-log is impacted.

SELECT
      RTRIM(pc.[instance_name])  AS [instance_name]
    , RTRIM(pc.[counter_name])   AS [counter_name]
    , pc.[cntr_value]            AS [cntr_value]
FROM sys.dm_os_performance_counters AS [pc]
WHERE (pc.[counter_name] = 'Log File(s) Size (KB)' AND pc.[instance_name] IN (DB_NAME(), 'tempdb'))
OR (pc.[counter_name] = 'Log Bytes Flushed/sec' AND pc.[instance_name] IN (DB_NAME(), 'tempdb'))
OR (pc.[counter_name] = 'Log File(s) Used Size (KB)' AND pc.[instance_name] IN (DB_NAME(), 'tempdb'))
OR (pc.[counter_name] = 'Log Growths' AND pc.[instance_name] IN (DB_NAME(), 'tempdb'))
OR (pc.[counter_name] = 'Percent Log Used' AND pc.[instance_name] IN (DB_NAME(), 'tempdb'))
ORDER BY pc.[instance_name], pc.[counter_name]

We can see from the results output (from the full script) that my laptop took around 40 seconds to remove all 1.2M rows.

Found 1234567 row(s) before test
Removed 50000 row(s) in loop number 1
Removed 50000 row(s) in loop number 2
...
Removed 50000 row(s) in loop number 24
Removed 34567 row(s) in loop number 25
Completed all DELETE cycles in 39536 milliseconds
Found 0 row(s) after test

But what impact did that have on the transaction log?

Results of first batch deletes on transaction log growth

We can see that our transaction log (which started off at 200 MB) had to grow 11 times (10% growth each time) and grew to 379 MB.

An Alternative Approach

So, I wanted to find out whether DELETE TOP (@n) would work and in the process of getting bored waiting for different versions of my code to run, started using explicit transactions within the loop itself in conjunction with a CHECKPOINT as shown below. Like the previous example, this is a simplified version of the code and you can download the full version including all the logging here.

DECLARE @RowCount int; SET @RowCount = 1; -- Must be >; 0 to initiate the loop
--! Define the maximum number of rows to process in a single batch
SET ROWCOUNT 50000;

WHILE @RowCount >; 0
    BEGIN
        BEGIN TRAN;

        DELETE dbo.SimpleBatchDelete;

        SET @RowCount = @@ROWCOUNT;

        COMMIT TRAN;

        CHECKPOINT;
    END

And the results…


Found 1234567 row(s) before test
Removed 50000 row(s) in loop number 1
...
Removed 50000 row(s) in loop number 24
Removed 34567 row(s) in loop number 25
Removed 0 row(s) in loop number 26
Completed all DELETE cycles in 32023 milliseconds
Found 0 row(s) after test

So although this wasn’t significantly faster (and indeed timings varied considerably on my laptop), the most interesting difference was in the effect on the transaction log:

Results of alternate batch deletes on t-log growth

We can see from the above screen shot that this alternative approach did not force any transaction-log growth at all. Obviously batch size would have had something to do with this but the batch sizes are the same in both examples. Further analysis proved that it was the CHECKPOINT, not just the use of explicit transactions that ensured we never used more than the pre-existing amount of t-log space.

Summary

This post wasn’t about performance although in general, the second approach was typically 10-20% faster, rather it was about stopping uncontrolled t-log growth during large DELETEs.

According to BOL, “A checkpoint writes the current in-memory modified pages and transaction log information from memory to disk and, also, records information about the transaction log”. As Gail Shaw writes here

When changes are made to a database, whether it be in an explicit transaction or an auto-committed transaction, those changes are first written (hardened) to the log file and the data pages are changed in memory. Once the record of the changes is in the log, the transaction is considered complete. The data pages will be written to the data file on disk at a later time either by the lazy writer or by the checkpoint process.

Gail goes on to point out that when a database is using SIMPLE recovery, because the checkpoint process forces dirty data pages to be written to disk, the part of the t-log that records those changes becaomes available for re-use.  What this means for us in practice is that using CHECKPOINT after each transaction allows the t-log space to be re-used instead of new transactions being appended to the t-log resulting in further growth.  This approach won’t work on a database that uses FULL recovery and so will not be suitable for most production environments.

Further reading…

Finally, as promised here are the additional references to Adam Machanic’s Numbers table use cases.

Comments on this entry are closed.

Previous post:

Next post: