Post reply

Deleting records from very large table and users experiencing slowness and low performance

  • August 9, 2018 at 9:54 am

    #358048

    Good Morning Experts,
    There is a very large table having 8 million records and I am deleting 6 million records from it. Users started complaining that they are experiencing slowness and low performance. Could you please tell me what should i do ?

  • August 9, 2018 at 10:40 am

    #2001083

    Stop the deletion!!!  Are you just doing a straight delete from ....?  This will lock the table, even worse if there are foreign keys with cascade delete.  You should put this in a loop and delete it in small batches (you transaction log will be happier too).  You'll have to test to find where the sweet spot is instead of using 1000, but it's a start

    declare @rows int = 1

    while @Rows > 0    
        begin

            DELETE TOP (1000)
            FROM [MyTab]
            WHERE YourConditions

            set @Rows = @@ROWCOUNT

        end

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 9, 2018 at 11:10 am

    #2001101

    A better way is to copy out what you want to keep, truncate or drop the original table, copy back in or rename the table to the original name;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply