I have a logging database with a few lookup tables and one massive event table that has 79 million rows. On busy days my code inserts over 3 million rows, so the event table would be enormous in production if not for the fact that every half hour I copy the latest data to an archive database on a separate server and then every night delete all rows more than one month old from production.

The problem is that these deletes are now taking over 10 hours to run. I don't think they cause any blocking as long as I put 100 ms delay between each delete statement (script below), but I feel like things could be improved. Should the logging db be in snapshot isolation mode? We typically write the row and then update the EndTime column after the function has completed. Any suggestions on how to improve the process and/or speed up production would be appreciated.

DECLARE @CutoffDate datetime = DATEADD(DAY, -30, GETDATE()), @Time datetime, @Stop bit = 0, @RowCount int

WHILE (@Stop = 0)
    SET @Time = GETDATE()

    DELETE TOP (100) af 
    FROM ApplicationEvent ae
    JOIN ApplicationFault af ON af.ApplicationEventID = ae.ApplicationEventID
    WHERE LogTime < @CutoffDate 

    DELETE TOP (100) ae 
    FROM ApplicationEvent ae
    LEFT JOIN ApplicationFault af ON af.ApplicationEventID = ae.ApplicationEventID 
    WHERE LogTime < @CutoffDate AND af.ApplicationFaultID IS NULL

    SELECT @RowCount = @@ROWCOUNT
    SELECT @Stop = CASE @RowCount WHEN 0 THEN 1 ELSE 0 END 


    WAITFOR DELAY '00:00:00:100';   

Here's the schema.

CREATE TABLE [dbo].[ApplicationEvent]( [ApplicationEventID] [int] IDENTITY(1,1) NOT NULL, [LogTime] [datetime] NOT NULL, [TransactionID] [int] NULL, [TransactionType] [int] NULL, [EventID] [int] NOT NULL, [EventLevelID] [smallint] NOT NULL, [StartTime] [datetime] NOT NULL, [EndTime] [datetime] NULL, [IPAddress] nvarchar NULL, [UserName] nvarchar NULL, [ApplicationID] [smallint] NOT NULL, [EventInformation] nvarchar NULL, [HostName] nvarchar NULL, [ApplicationSourceId] [smallint] NULL, CONSTRAINT [PK_ApplicationEvent_1] PRIMARY KEY CLUSTERED ( [ApplicationEventID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

2 Answers 2


Two suggestions come to mind.

One is that the clustered index on both ApplicationEvent and ApplicationFault above be on the LogTime column. Assuming the data is posted to the table in chronological order, you'll have reduced fragementation from page splits and benefit from range scans when purging out older time periods.

The second builds on the first to implement parititioning based on the LogTime column. Instead of the relatively expensive I/O during the delete operations, you can implement a sliding window which would 'slide' older time periods out the main table with ALTER TABLE commands, effectively dropping unneeded dates via essentially meta commands.

  • That sounds like a great idea, I've never used partitioning but have long wanted to. I don't know when I could find the downtime to switch the PK though.
    – influent
    Commented Apr 23, 2014 at 23:11
  • I'm not sure if I should make a composite nonclustered PK using ApplicationEventID and LogTime or just a clustered PK on LogTime with an index on ApplicationEventID or what. ?
    – influent
    Commented May 2, 2014 at 23:33
  • I can't just make a clustered PK on LogTime since there are duplicates.
    – influent
    Commented May 2, 2014 at 23:59
  • You can make a clustered composite key based on both ApplicationEventID and LogTime. The IDENTITY on ApplicationEventID should be enough to guarantee uniqueness for the PK constraint as well. I'd try that variation and one with the existing PK on ApplicationEventID becoming non-clustered and making a new clustered, non-unique index on LogTime for use in the partition function.
    – MattyZDBA
    Commented May 4, 2014 at 1:39
  • If I make a composite key don't I then have to have both columns in the tables that reference the PK with a foreign key? I tried the clustered non-unique index on LogTime but then I can't use SWITCH to remove a partition because it says the PK is not partitioned, but I can't make the PK partitioned without including LogTime in it.
    – influent
    Commented May 13, 2014 at 22:21

The problem is that these deletes are now taking over 10 hours to run.

if you are not on Enterprise edition - as Partitioning - switching in and out is only supported in Enterprise edition, I would suggest you to create and ordered view on the top of table that you are deleting data from. This will incur less I/O and generate minimal T-Log.

Founded the link written by SQL CAT team about Fast ordered delete technique of using a view or a CTE.


-- tested to delete 27 million and it took 24 mins to delete

select top (100000) * from TABLE_TO_DELETE

----  Caution : perform actual deletes now !!
--- start an Explicit transaction to avoid Log flushes.



---- if FULL Recovery then do T-log backup
---- if SIMPLE Recovery then perform a CHECKPOINT

COMMIT TRANSACTION  --- in case if we want to cancel in between, the deletes are committed in the log already

Should the logging db be in snapshot isolation mode?

You have to test it out depending on your workload. Read committed Snapshot isolation (RCSI) has a tempdb overhead as it has to do a lot of row-versoning. The Database engine will store a version of the previously committed image of the row in tempdb.

ON a highlevel - RCSI - selects do not lock data during a read operation and Read transactions do not block write transactions and vice versa.

  • Thanks. Do you mean I should use an indexed view? I don't see how a regular view with ORDER BY would help.
    – influent
    Commented Apr 23, 2014 at 23:53
  • It would help as in my case I created an ordered view on the top of table that had 80 million rows and deleted 27M out of it. There was a Microsoft CAT article FAST ORDERED DELETES - but the link is broken now
    – Kin Shah
    Commented Apr 24, 2014 at 2:05
  • Good to know, but it didn't help me, and neither did using a derived table. Adding an index on ApplicationEventID in the ApplicationFault table made a huge difference though, and I think I can get away with that without slowing down inserts too much. I still should work on implementing sliding window partitioning though.
    – influent
    Commented Apr 24, 2014 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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