I have the following scenario:

A huge table, that is also the most use table by far in the system. By huge I mean it is huge in number of rows and also huge in actual disk size, because it has 2 byte array columns that saves files from our system.

The problem is when I need to remove a large number of rows from it. First I tried just a single delete with in statement, but is froze the whole table (inutilizing the system with it), for about 45 minutes, when I decide to restart the database.

Then I tried deleting by chunks of 10 registries, and tried deleting 8k+ rows. It went really slow, and everywhere that got data from that table was slow too, and when it reached about 700 rows deleted, I started another process that used that same table, and got a deadlock.

How should I make these mass deletions from this table? I thought about splitting the table in 2, one for the 2 byte arrays that contains the most part of table volume, and other with the rest of the informations (that are simple things like varchars, integers, datetimes and so on). Would doing this benefit me on these cases of deletion? Or the number of rows would still cause these locks on the table?

The version of SQL is Microsoft SQL Server Standard (64-bit) 12.0.5000.0.

  • What do you mean saying "I need to remove a large number of columns from it"? Do you really need to remove columns or rows? Delete does not remove any column, alter table drop column does, but dropping coluns is metadata only changing operation, so you need to delete rows? What is your db recovery model? Do you want to leave il place more data than you want to delete or vice versa? – sepupic May 11 '17 at 13:11
  • Sorry, I meant rows, i'm gonna edit the question. – Luiz Eduardo Simões May 11 '17 at 13:12
  • My recovery model is simple, but this will be implemented in a db with full. And I need to leave more rows than deleting. – Luiz Eduardo Simões May 11 '17 at 13:15
  • Are you using enterprise edition of SQL? Are you deleting files based on date or some other criteria? Are there foreign key constraints with cascade deletes? Do you get good performance when you try and select the same rows you would delete? – Jonathan Fite May 11 '17 at 13:29
  • I believe it is stardart, not enterprise edition, the files are being delete simply by a delete from myTable where row_pk in (list of pks). The select of this large chunk of data is also slow because of the byte array columns that I mention in the question. And there is no cascade delestes coming from it. – Luiz Eduardo Simões May 11 '17 at 14:27

First off, a couple of articles:

You didn't mention the version of SQL Server, so I'm not sure if the second one is relevant to you, but it might be to other readers. I'll focus on the former.

Typically, deleting a lot of data causes a wide array of symptoms:

  1. more rows affected = greater likelihood of lock escalation
  2. longer transactions = lengthy blocking (and sometimes deadlocks, as you've found)
  3. heavy writes to transaction log = write latency (including growth events)

So my idea in that article was to lock fewer rows by deleting in "chunks" - each delete operation (or a finite number of them) would be in a transaction, thereby reducing the number of locked rows, the length of the transaction, and the impact to the log.

So I might suggest trying something like this:

SELECT key_column
  INTO #work
  FROM dbo.big_table
  WHERE -- however you identify rows to delete;

CREATE CLUSTERED INDEX x ON #work(key_column);

DECLARE @rc  int = 1, 
  @counter   int = 1,
  @batchsize int = 100; -- this may or may not be an optimal chunk size

WHILE @rc > 0

  DELETE TOP (@batchsize) t
    FROM dbo.big_table AS t
    INNER JOIN #work AS x
    ON t.key_column = x.key_column;

  SET @rc = @@ROWCOUNT;


  SET @counter = @counter + 1;

  IF @counter % 10 -- or maybe 100 or 1000

The checkpoints make sure that the log space from the transaction can be re-used (the second one ensures the log properly "wraps around"). In your full recovery environment, you'd just need a single BACKUP LOG command there... or you might consider switching to simple if you can time this maintenance work very closely to your regularly scheduled full backup where you can restart the log chain.

The trick is to find the sweet spot of balancing the reuse of the log and the work the checkpoint or log backup has to do. (In my article the frequency of checkpoints actually caused the overall operation to be slower, which might be ok, it doesn't really matter how long this takes if it truly acts like a background process - the problem isn't duration, it's interference. I just didn't fish around for the sweet spot.)

The batch size effectively limits the number of rows locked within any given transaction and reduces the amount of work required by that transaction. You can use delayed durability here if you have a modern enough version; the biggest risk is that you lose a transaction, but since what you "lose" is data removal, you haven't really lost anything.

  • Could you please explain the difference of your chunk approach to my approach? I am not a DBA, i am a programmer, so it is a bit confusing to me. – Luiz Eduardo Simões May 11 '17 at 14:32
  • I don't know, you didn't show your actual approach, you only partially described it. If you show your actual code it might help illustrate differences. – Aaron Bertrand May 11 '17 at 14:52
  • @AaronBertrand What do you think about adding WAITFOR command? Is it good idea to leave some time between transactions in terms of concurrency? – Paweł Tajs May 12 '17 at 6:17
  • It was just a loop with this command delete from myTable where row_pk in (list of pks) but 10 pks each time. – Luiz Eduardo Simões May 12 '17 at 12:03

If you are deleting more than a couple % of the rows in a table, it is frequently quicker to:

  1. CREATE TABLE table_temp as select * from table where ID IN (records to keep)
  2. Alter the current table to be table_old
  3. Alter table_temp to be table
  4. Drop table_old
  • This can work if rows to keep <= rows to delete. I would say the trade-off threshold is much higher than a couple %. – Aaron Bertrand May 11 '17 at 13:47
  • Agree, @AaronBertrand, especially on TB-sized databases. – BradC May 11 '17 at 13:49
  • If you go this route, remember to add indexes back. – SqlACID May 11 '17 at 14:31
  • Furthermore if you are designing around this situation (rather than already having the problem, which is the case of the original question hence not proposing this as an answer) and the deletes follow an obvious pattern (deleting anything older than X months perhaps) then this is a place where partitioning would work well. Assuming the partitioning divides align with what you want to delete you can just swap out whole partitions pretty quickly. – David Spillett May 11 '17 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.