deleting large volumes of rows from table efficiently in SQL Server [duplicate]

Question

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.

Answer 1

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:

• Brent Ozar - Sargability: Why %string% Is Slow
• Index usage by %Like% operator – Query Tuning

Answer 2

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

CREATE TABLE #DelTest (ID INT IDENTITY, name NVARCHAR(128));
INSERT INTO #DelTest (name) SELECT name FROM sys.objects;
SELECT COUNT(*) TableNamesContainingSys FROM deltest WHERE name LIKE '%sys%';
go
DECLARE @HowMany INT;
DECLARE @RowsTouched INT;

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

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

   SET @RowsTouched = @@ROWCOUNT;

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

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.