February 6, 2013 at 4:34 pm
How do we avoid blocks or performance down grade against a table that has deletes and inserts against it?
Every 2 seconds there is about 4000 inserts and I want to delete about 200,000 records per hour.
Help is much appreciated. Thanks.
February 6, 2013 at 6:46 pm
You are close to your problem, so may I inquire:
a). Your deletion of 200,000 records per hour, is this to be continuous that is for 24 hours per day, 7 days per week ?
b). Basically the same question for the insert activity.
c). Using the same criteria as that which you will use for the deletion, how long does it take you to select 200,000 records ?
With this further information some one might be able to assist you.
February 6, 2013 at 8:32 pm
Sounds like you have two problems. Large inserts and even larger deletes. I'd do something like this:
1. First create a partitioned table.
MSDN: http://msdn.microsoft.com/en-us/library/ms188730(v=sql.105).aspx
2. Import the 4000 inserts into a heap table, ordered if possible in the same order as the clustered index of the partitioned table.
3. Insert the contents of the heap table by using ALTER PARTITION to add the rows by creating a new partition on the target table. Then truncate the heap table for the next cycle.
Paul White's example (SQL MVP):
http://www.sqlservercentral.com/Forums/FindPost1236713.aspx
3. Then, for deletion, use the same process in reverse by deleting entire partitions rather than row-by-row deletion. You won't get any faster insertions and deletions than by using this process.
MSDN: http://technet.microsoft.com/en-us/library/aa964122(SQL.90).aspx
If the partition methods won't work for you or if the rows to be deleted need to be archived and not actually deleted you can MOVE them with the little known OUTPUT method of the DELETE statement. This will only require one pass through the data rather than a SELECT INTO and then a DELETE.
Using Ben Thul's example you can actually MOVE rows in one operation:
DELETE dbo.fooActive
OUTPUT
DELETED.Col1,
DELETED.Col2,
DELETED.Col3
INTO dbo.fooInactive
WHERE Col1 = 'fubar'
When using DELETE you should drop any indexes and FKs before doing the deletion, and then it's best to do the deletes in batches. It takes some experimentation to determine the ideal batch size, but do so by adding a TOP(x) to your DELETE statement via a derived table to simplify the query plan and avoid table scans.
DELETE foo FROM
(SELECT TOP (10000) *
FROM foo ORDER BY col1) foo
Once the delete is done, re-create the indexes and FKs.
February 7, 2013 at 1:18 am
JP10 (2/6/2013)
How do we avoid blocks or performance down grade against a table that has deletes and inserts against it?Every 2 seconds there is about 4000 inserts and I want to delete about 200,000 records per hour.
Help is much appreciated. Thanks.
Will it be single delete of 200,000 records per hours, or delete will happen as seprate 200,000 operations during the hour?
February 7, 2013 at 10:07 am
JP10 - Here's a timely article on table partitioning posted in today's SQL Server Performance Newsletter:
http://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/
February 7, 2013 at 10:58 am
Thanks all for the information much appreciated. Yes, this will occur every hr for 24hrs. The select of 200,000 was not using the indexs properly so it took about 3seconds for the return. I corrected the select (coworker was not using the proper indexs) to use the indexs properly now I'm getting the seeks and returns instantly.
So, buy using the correct indexs the delete should go much faster. I have been told now the deletes need to be in the millions not 200,000, but with the use of indexs it should be much faster correct? The concerns are transaction log growing but they do a transaction log backup every hr so this will be shrunk after an hr. Indexs may get fragmented, but we have a janitor that rebuilds indexs if they get fragmented over like 30% so we should be good there.
Any other concerns do you guys see that I am missing?
Appreciate the help.
February 7, 2013 at 4:35 pm
JP10 (2/7/2013)
Any other concerns do you guys see that I am missing?
Do not shrink transaction log file.
Just empty it by running log backup, but do not shrink it.
Release/alocate space on disk for db files are extremely expensive operations and may cause significant delays.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply