Problems deleting 20,000+ rows from table with 30 million rows+

Question

Problems deleting 20,000+ rows from table with 30 million rows+

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

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

Johan Bijnens SSC Guru Points: 135590 More actions · Answer 1

can you post the DML you use for this delete ?

If it is a single delete statement that just performs like a table scan to do its stuff, that may indeed cause lock escalation.

Can you chop the delete in batches ?

SET NOCOUNT ON

SET ROWCOUNT 1000

WHILE 1 = 1

BEGIN

DELETE TableName WHERE

IF @@ROWCOUNT = 0 BREAK

CHECKPOINT

END

SET ROWCOUNT 0

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Paritioning may help, without seeing more info on the table design and delete statement I can't be more specific than that.

The async model may very well help. Scheduling the deletes to happen overnight will reduce their impact on normal operations.

Deletes of a lot of rows do take long. Often what's recomended it to batch the deletes and only do a couple thousand at a time. Something like this (untested)

DECLARE @Done int

SET @Done = 0

SET ROWCOUNT 5000

WHILE (@Done = 0)

BEGIN

DELETE FROM SomeTable WHERE SomeConditions -- will delete max 5000 due to the rowcount setting

IF @@ROWCOUNT = 0

SET @Done = 1

END

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

I know every case is different, but deleting 20,000 doesnt sound as though it should be taking anything like 5 minutes.

A few thoughts:-

Assuming you are doing a DELETE... WHERE..., are the WHERE columns indexed?

Do any other tables have constraints to the table you are deleting from, and if so, are the constraining columns indexed?

Do you have any delete triggers on the table?