3
\$\begingroup\$

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?

\$\endgroup\$
  • 1
    \$\begingroup\$ How about computing cast(dateadd(day, -7, GETUTCDATE()) as date) once and reuse it? \$\endgroup\$ – Heslacher Jul 30 at 9:09
  • 1
    \$\begingroup\$ If this is a production environment, I would suggest to use a commercial tool that handles billions of logs for you: splunk.com \$\endgroup\$ – dfhwze Jul 30 at 9:18
  • 1
    \$\begingroup\$ If a commercial tool is not an option, consider creating an unclustered index on [Timestamp]. \$\endgroup\$ – dfhwze Jul 30 at 9:20
  • 1
    \$\begingroup\$ some other options: stackoverflow.com/questions/24213299/… -> it also depends on whether you are deleting most of the records from the table, or keeping most. \$\endgroup\$ – dfhwze Jul 30 at 15:58
  • 1
    \$\begingroup\$ @dfhwze I have an idea! I'll put all ids that I want to delete into a cts and then make a delete + join with that on the index without searching each time. \$\endgroup\$ – t3chb0t Jul 30 at 16:02
2
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ wow, this is a lot of interesting stuff! It'll take some time to study it and do experiments. I'll let you know when I implemented these suggestions. \$\endgroup\$ – t3chb0t Aug 22 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.