I have to move
some of data from main reporting table (~100M rows) to archive server (each few M rows to different target database..).
I did write a query, that for every 5000 rows that match my criteria copies the data (selects and inserts into archive table) and then deletes data from the source table.
As for each 5000 row select to temporary table lasts about 5 mins, the last step of transaction, DELETE, lasted 8 hours for only 25000 rows
As I have to migrate ~20 millions rows till the end of the month, I got a serious problem.
At first I have a temp table (on destination server, as the query runs from destination server)
create table #tran_nr(nr int not null)
create unique clustered index ix_1_temp_nr on #tran_nr(nr)
and insert data into it (~5 mins for 5k rows).
then, after copying, I do the following:
set @sql=N'delete from ' + @src_table_trans +
N' where tr_tran_nr in (select nr from #tran_nr)'
EXEC sp_executesql @sql
And that delete lasts way too long.
On the source table (from which I am deleting) there are a lot of indexes. Especially, the tran_nr
column is the primary key and clustered index.
Are all the indexes on source table rebuilt when deleting? How can I fasten the delete operation?
I cannot just copy the @src_table_trans and truncate the source etc. because the table is on production reporting server and it is used every 5 minutes by several jobs.
Source and destination tables are on different servers (it's a distributed transaction via DTC), however, the storage disks are on same lunn (SAN).
EDIT: when deleting, the disk I/O on the machine (with the source table) goes to 100%, but transaction log isn't growing at all.