I have a table that has gotten a little out of control. I am not a DBA per se but seem to recall that deleting a huge volume of rows in one shot can cause transaction log issues, hamper overall system performance during the delete, etc...

Is there an efficient way for me to create a job that deletes records in small batches to over hampering other access/performance and prevents problems with the transaction log?

This process can be quite slow is that makes a difference

For additional context, the delete criteria will be based on something like... where x like '%blah%'. Also, there is one clustered index and 5 non-clustered indexes.

  • Is there really no existing question about bulk deletes that covers all these issues? Sounds like a duplicate.
    – usr
    May 26 '14 at 22:05

You can break it up into chunks - delete in a loop; each delete iteration it's own transaction and then clearing the log at the end of each loop iteration. Finding the optimal chunk size will take some testing.

I suggest you take a look at this article by Aaron Bertrand, where he explains the details and runs tests for different scenarios, to show the impact (duration, transaction log): http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

As for the where x like '%blah%' delete condition - it will make the query not sargable (can't leverage the indexes to do an index seek). So even if you have an index to support column x, it's going to be a scan.

Resources for this:

  • Having a seek on a query that performs a delete operation of hundreds or thousands of rows does not seem like a goal you want to have. Identifying the rows is hardly the most expensive part of this operation... May 26 '14 at 22:21

It is fairly simple. Here is a framework to let you test.

INSERT INTO #DelTest (name) SELECT name FROM sys.objects;
SELECT COUNT(*) TableNamesContainingSys FROM deltest WHERE name LIKE '%sys%';
DECLARE @RowsTouched INT;

SET @RowsTouched = 1;
SET @HowMany = 5;

WHILE @RowsTouched > 0
   DELETE TOP (@HowMany)
   FROM #DelTest 
   WHERE name LIKE '%sys%';

   SET @RowsTouched = @@ROWCOUNT;

SELECT COUNT(*) TableNamesContainingSys FROM #DelTest WHERE name LIKE '%sys%';

EDIT: Of course opening wildcard searches are notoriously poor for finding rows, since the entire column has to be searched. Also, as pointed out in Aaron's post you need to manage the logs as well, either by being in SIMPLE mode or by doing log backups to keep the log files from growing excessively.

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