How to batch delete large table?

Question

How to batch delete large table?

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

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

lmacdonald Hall of Fame Points: 3763 More actions · Answer 1

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.

jude.pieries Default port Points: 1438 More actions · Answer 2

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).

lmacdonald Hall of Fame Points: 3763 More actions · Answer 3

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.

jude.pieries Default port Points: 1438 More actions · Answer 4

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

lmacdonald Hall of Fame Points: 3763 More actions · Answer 5

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