Mass deletes on a table without blocking inserts
-
Hi,
I have an audit table which receives anywhere up to 250000 insertions per day.
This data needs to be kept for three months where is may then be deleted from the table.
During the delete operation I cannot block any processes which are performing inserts for more than a handful of milliseconds.
To ensure I don't lock anything out for any length of time I'm declaring a cursor on the table and performing a fetch next from / delete from where current of loop until I reach the end of the cursor.
Is this the best approach? I have an ID on the table which I could scale with repeated delete operations and keep incrementing the ID however I'm concerned that the effeciency of the index would decrease over the duration of the operation due to the masses of deleted rows sitting in the upper leaves of the index.
-
I may be completely out of my senses.
But I feel that insert operations should not be locked by delete operations ideally.
What must be happening in your case is that sql server must be escalating the lock to save resourses on the server.
My suggestions are :
1) Perform you delete operations at a time when the database usage is at a minimum.
2) Keep u'r transactions as short as possible.This can be done by passing ids in batches of say 500. This will also help in quick rollbacks if a transaction fails.
Correct me If I am wrong.
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself. -
One alternative would be to use SET ROWCOUNT and have a set based delete query:
set rowcount 1000
declare @continue char(1)
set @continue = 'Y'
while @countinue = 'Y' begin
delete <table>
where <date more than 3 months>
if @@rowcount = 0 set @continue = 'N'
end
This will loop round and delete rows in batches of 1000 (or whatever you set rowcount to be). It probably be quicker than using a cursor and if you set rowcount small enough will not block another process.
Jeremy
-
Ok,
You have millions of rows and you need to delete some.
-- Create an index that covers the 'create date' or however you qualify older than 3 motnhs.
-- Cluster on that index if possible.
-- Delete daily (or even a couple of times a day) where event dt < getdate -90
This should be a small enough delete as to not lock the table. Reindex that table weekly
-
Previous posters said it all.
Maybe schedule deletes for 2:00 am (low activity).
-
Peely,
Once your deletes are up to date it seems possible that the number of daily deletes would be close to the number of inserts. How about an INSERT trigger to delete 1 or 2 rows at a time with each inserted row?
Dan B
-
Thanks everyone!
What I have typically found is the transaction times with repeated deletes goes up as the statistics on the index become more and more effected with the deleted data in the upper leaves. I'd prefer to perform a mass delete then update the statistics on the table once the process has completed. Is there anything fundamentally wrong with the cursor method I'm using? I find it actually the fastest approach when I'm removing so much data and a maintenance window is out of the question as this is a 24/7 system without any quiet period what-so-ever.
A clustered index is out of the question as there is a lock on the table during the statistics update and due to the masses of insertions this would need to be performed regularly to reorganise the table structure.
Cheers again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply