Archiving data from logging db (high writes, no reads) 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)
BEGIN
    SET @Time = GETDATE()

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

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

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

    --PRINT DATEDIFF(MS, GETDATE(), @Time)

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




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]
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.

e.g. 

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

CREATE VIEW vw_TABLE_TO_DELETE
as 
select top (100000) * from TABLE_TO_DELETE
where CONDITION
ORDER BY LogTime 

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

BEGIN TRANSACTION

DELETE
FROM vw_TABLE_TO_DELETE

---- 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.