Deleting millions of logs in batches

Question

I have a fast growing table with logs that I frequently have to delete (the server doesn't have much resources). It grows by at least two million entries every day. The database is a SQL Server 2012.

Currently I use this script to delete entries older than 7 days:

set nocount on;
declare @r int = 1
while @r > 0
begin
    begin transaction;  
        delete top(10000)   
        from [Logs]
        where 
            cast([Timestamp] as date) < cast(dateadd(day, -7, GETUTCDATE()) as date))
        set @r = @@ROWCOUNT;
    commit transaction;
end

When I don't do it daily then it runs for a couple of hours (3-5) blocking other tasks that usually exit with timeouts (they need it for reporting).

I use transactions because I sometimes need to stop it before it's finished and this is easier to do with smaller batches. Otherwise there is too much to rollback.

The table is a pretty normal log:

CREATE TABLE [dbo].[Log](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Timestamp] [datetime2](7) NOT NULL,
    [Environment] [nvarchar](50) NOT NULL,
    [Logger] [nvarchar](50) NOT NULL,
    [Message] [nvarchar](max) NULL,
    [Exception] [nvarchar](max) NULL,
 CONSTRAINT [PK_Log_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

---

Is there a way to make this script faster? Am I doing anything terribly wrong here?

Answer 1

Here is another recent Code Review question that has a lot of similarities to yours: Daily SQL job to delete records older than 24 hours.

I think I would recommend three things here:

1. Snapshot isolation
2. Not using TOP
3. Doing the work in another table

---

Snapshot isolation

Snapshot isolation is a really cool tool that effectively prevents most things from blocking readers. As a result, even if your delete is running long, report-writers can still hit your table and they'll see the most recent, valid data. Then once your delete finishes, they'll start seeing the data without your deleted rows. This lets you do whatever you need to do without having to worry as much about end-users. It doesn't make your code faster, but it will somewhat reduce the need for it to be.

---

Not using TOP

Because you're using TOP, you effectively force all of the data to be re-sorted every time. Additionally, TOP will usually introduce row goals. This isn't necessarily a bad thing, but it may choose less-ideal plans in the interest of getting a subset of rows as quickly as possible.

Because your Id column is an indexed IDENTITY column, and because we're deleting the oldest data (I'm assuming you don't generally update old data), you can do something like this:

DELETE FROM [Logs]
  WHERE Id BETWEEN @LowestCurrentIndex AND @HighestCurrentIndex
    AND CAST([Timestamp] AS date) < CAST(DATEADD( DAY, -7, GETUTCDATE()) AS date);

This assumes you can maintain @LowestCurrentIndex and @HighestCurrentIndex as the range of values to currently consider. This will get you nice index accesses as well.

A potential enhancement is to get a separate table that has all potentially affected rows, like so:

SELECT Id
  INTO #OldData
  FROM [Logs]
  WHERE CAST([Timestamp] AS date) < CAST(DATEADD( DAY, -7, GETUTCDATE()) AS date);

Then you can just join between the two (and if you have an index on #OldData.Id it'll be a great merge-join) with the same bounds logic.

DELETE [Logs]
  FROM [Logs]
    INNER JOIN #OldData OldData
      ON [Logs].Id = OldData.Id
  WHERE OldData.Id BETWEEN @LowestCurrentIndex AND @HighestCurrentIndex -- This could be on either table

---

Do the work in another table

If copying the data is less expensive than deleting it (very possible):

1. Copy data newer than 7 days into another table
2. Rename the original table to something else
3. Rename the table from #1 to the original table's name
4. Truncate the original (now renamed) table from #2

This likely will have less index maintenance and less likely to hit the transaction log (#1 is minimally logged, #2/3 don't require any transaction space, #4 is minimally logged)

Snapshot isolation works nicely with this approach.