locked
Issue with massive delete using stored procedure with replicate execute only in SQL 2008 R2 transacttonal replication RRS feed

  • Question

  • We delete massive data once in a while.  Since we use push transaction replication on SQL 2008 R2 enterprise, to mininze the replication traffic, we put the delete statement in a stored procedure and replicate the sp as 'execute' only.   This works very well when i am deleting say 1 to 10 million records on a narrow table.   When it comes to delete 100 million records in a wider table, the delete would eventually finish but i observed very odd delete activity behavior in the subscriber servers while the delete is happening.

    The delete statement in the store procedure is just like this:

    Delete from tableA where KeyID between @beginKey and @EndKey 

    The keyID is a primary cluster key and I execute the stored porcedure this way.

    Exec dbo.sp_deleteTableA @beginKey = 100000000, @EndKey = 200000000

    When I initiate the execution of sp on the publisher db, the sp finished in about 15 mins.  On the two subscriber (two physical servers different than the publsher), the sp would take extremely long to finish.  When I ran a DMV statement to see what was running on the subscriber, i saw the delete statement in the SP was running and when I checked the record count, it was gradually decreasing.  The weird thing is that as soon as my tracking record count showed the delete close to being done, All of a sudden, the tracking record count bounced back the initial count as if nothing has been deleted.  I didn't see any rollback statement in the DMV at all.   The delete statement just kept on running (or re-running).  This cycle of delete continues for 3~5 times and eventually it finished. But it took almost 1 hour and half to complete compared to 15 mins on the publisher server.   

    I did this during a downtime so nobody would touch the table I was deleting.

    Can someone offer any insight as of what might have happened and a better way to do such as massive delete in this replication setting?

    OD


    Ocean Deep

    Wednesday, November 6, 2013 10:12 PM

Answers

  • How are you doing your tracking count? If it involved the use of the no-lock hint it is likely inaccurate. I suspect you are getting an inefficient execution plan on the subscriber side. You will need to study it and see if you can't figure out another index to put on or a hint to make it work better. I have seen seeks go to scans in the normal replication delete stored procedures.

    Now there could be a few other factors - replication will retry a batch process on failure. The proc itself might have failed, or something on further down the chain. This would resubmit the entire batch for reprocessing.

    Using profiler with the error collection will give a deeper insight into what is occurring.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Proposed as answer by Sofiya Li Tuesday, November 12, 2013 1:37 AM
    • Marked as answer by Sofiya Li Friday, November 15, 2013 7:47 AM
    Thursday, November 7, 2013 12:53 PM
    Answerer

All replies

  • You could pause/stop the publication, do the source table delete, and finally reinitialize the target subscription(s)...

    M.Janzou


    • Edited by mjanzou2 Wednesday, November 6, 2013 10:53 PM
    Wednesday, November 6, 2013 10:27 PM
  • A correction on the # of records I was deleting, it was 10 millions not 100 millions.  I had an extra zero in the key ID

    OD


    Ocean Deep

    Wednesday, November 6, 2013 10:42 PM
  • How are you doing your tracking count? If it involved the use of the no-lock hint it is likely inaccurate. I suspect you are getting an inefficient execution plan on the subscriber side. You will need to study it and see if you can't figure out another index to put on or a hint to make it work better. I have seen seeks go to scans in the normal replication delete stored procedures.

    Now there could be a few other factors - replication will retry a batch process on failure. The proc itself might have failed, or something on further down the chain. This would resubmit the entire batch for reprocessing.

    Using profiler with the error collection will give a deeper insight into what is occurring.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Proposed as answer by Sofiya Li Tuesday, November 12, 2013 1:37 AM
    • Marked as answer by Sofiya Li Friday, November 15, 2013 7:47 AM
    Thursday, November 7, 2013 12:53 PM
    Answerer