Post reply

How to batch delete large table?

  • June 17, 2015 at 8:43 am

    #1806088

    Thank you. I would prefer not to use temp tables. I have to figure out why it's so slow compared to staging, once I do that I will try a delete based of a where statement similar to that.

  • June 17, 2015 at 5:47 pm

    #1806240

    Few things to consider for the slowness of the queries

    1.) Probably need to check the fragmentation on this table ( rebuild/reorganize the indexes)

    Note the limitations depending on the SQL server edition. Enterprise has the rebuild online option

    2.) If 1 does not work, look at updating the states of the table

    3.) If the existing indexes don't support predicates in the where clause , looking to adding the required indexes

    As for the migration, the suggestion from Andrei Hetel seems to be good approach. This should causes little trouble in my mind, again , hope you don't have a restriction with storage.

    Oh , and SQL server partitioning can be put into the mix ( Limited to SQL Enterprise).

  • June 18, 2015 at 6:57 am

    #1806348

    I will try making a new table and copying in the events I want to keep as plan b. We just built a new index which hopefully will help. I have yet to see the fragmentation of the current ones because when I right click on the index and look at the properties, SQL Studio goes into a not responding state and I have to restart it. I have also noticed that there is a lot of activity on this table, it's getting updated and deleted from more frequently then I thought and most of the time my queries end up being suspended. The wait types have generally pointed towards an I/O bottleneck.

  • June 18, 2015 at 5:33 pm

    #1806573

    You can use the sys.dm_db_index_physical_stats DMV to get the fragmentation of the index

  • June 19, 2015 at 7:52 am

    #1806684

    We were able to add an index that allows the following statement to execute within a reasonable time. However what I don't get is each time I run it, it deletes less then 20,000 rows. The last time I ran it it deleted 600 something and then I ran it again immediately and it deleted 588. Why is deleting so few?

    SET NOCOUNT ON

    DELETE TOP (20000)

    FROM [Logging].[EventLog]

    WHERE ( WeeksToRetain > 0

    AND DATEADD(ww, WeeksToRetain, EventDate) < GETDATE()

    )

    --print @@rowcount

Viewing 5 posts - 16 through 19 (of 19 total)

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