We are using full recovery model in SQL Server. We have a job which merges from a staging table to the final table. The staging table is holding millions of rows. The final table is also huge with millions of rows. We are merging in batches of 10,000 rows.
The pseudo code is given for a single batch below:
BEGIN TRANSACTION
DELETE TOP 10000 *
FROM <Staging Table>
OUTPUT deleted.* INTO @TableVariable
MERGE INTO <Final Table>
USING @TableVariable
COMMIT TRANSACTION
The problem is, the batch operation is getting slower, for every new batch. When we restart the server, the batches are getting faster again. The transactions are not getting written to disk also and taking very long time to insert to disk. We suspect it to be problem with transaction log. When we reduce the batch size, more transactions are happening and batches are slowing down even more.
Is there a way to improve the performance of this kind of batched delete & merge operation? Do you recommend to use CHECKPOINT to force in full recovery model?
What we did is, instead of forcing the CHECKPOINT process, we introduced artificial delay in the WHILE LOOP, so that transactions are not getting throttled.
We were able to overcome the problem for out of memory issues due to transactions throttling in the SQL Server environment. We had millions of rows in the staging table. This 10,000 batch & delay introduced made sure that we are not overloading the server. There were people accessing the server.
DECLARE @RowCount INT;
SET @RowCount = (SELECT COUNT(*) FROM StagingTable);
WHILE (@RowCount > 0)
BEGIN
BEGIN TRANSACTION
DELETE TOP 10000 *
FROM <Staging Table>
OUTPUT deleted.* INTO @TableVariable
MERGE INTO <Final Table>
USING @TableVariable
COMMIT TRANSACTION
WAITFOR DELAY '00:00:10'; --artificially introduce 10 seconds delay
SET @RowCount = (SELECT COUNT(*) FROM StagingTable);
END