Deleting old data sounds simple until the table contains more than 18 million rows and the application still needs to remain usable.
We recently needed to remove a large volume of historical activity data from an Azure SQL database running on the S1 tier. The first versions of the cleanup were technically correct, but they were not operationally acceptable. They consumed too many resources, ran for too long, and created unnecessary risk for the application sharing the database.
The version that worked reliably was not the most aggressive. It deleted 25,000 rows approximately every six seconds, giving us steady progress without overwhelming the database.
The important lesson was not a magic batch size. It was that a large production delete is a workload that needs to be designed, indexed, observed, and adjusted.
Why One Large Delete Is Usually the Wrong Goal
A single statement that removes millions of rows may look efficient because it avoids application-side looping. In production, however, that transaction can generate substantial log activity, hold locks for a long time, increase blocking, and make rollback expensive if the operation fails.
On a constrained Azure SQL tier, the maintenance operation also competes directly with normal application traffic for CPU, data I/O, and log-write capacity.
Breaking the work into committed batches limits the size and duration of each transaction. It also creates natural control points where the process can pause, measure database health, or stop safely.
Batching alone is not enough. If SQL Server must scan a large portion of the table to find every new batch, the process can become slower as it progresses.
Make Each Batch Easy to Find
Our most important improvement was aligning the cleanup with the table’s clustered key. Instead of repeatedly asking SQL Server to find 25,000 qualifying rows across the full table, we selected a bounded key range and deleted within it.
DECLARE @BatchSize int = 25000;
DECLARE @CutoffUtc datetime2 = '2025-01-01T00:00:00';
WITH DeleteBatch AS
(
SELECT TOP (@BatchSize) UserActivityRecordId
FROM dbo.UserActivityRecords
WHERE UserActivityRecordId > @LastProcessedId
AND CreatedDateUtc < @CutoffUtc
ORDER BY UserActivityRecordId
)
DELETE records
FROM dbo.UserActivityRecords AS records
INNER JOIN DeleteBatch AS batch
ON batch.UserActivityRecordId = records.UserActivityRecordId;
The exact statement will vary with the retention rule and schema. The broader goal is consistent: give the optimizer an efficient access path, make progress deterministic, and avoid rediscovering the same ineligible rows during every iteration.
The continuation value should advance based on the key range examined, not simply the last row deleted. Otherwise, a sparse set of qualifying rows can cause repeated work or skipped ranges. Persisting checkpoints is also useful when a cleanup must survive deployment restarts or planned pauses.
The Supporting Index Determines Whether Batching Helps
Looping around an inefficient query only runs it more often.
Review the actual execution plan for the batch-selection query. If the retention predicate uses a date while ordering by the clustered key, the database may need an index that supports that combination. The right index depends on selectivity, existing indexes, and the cost it adds to normal writes.
CREATE INDEX IX_UserActivityRecords_CreatedDateUtc_Id
ON dbo.UserActivityRecords
(
CreatedDateUtc,
UserActivityRecordId
);
Do not copy this index blindly. If the cleanup follows clustered-key ranges first, reversing the key order may be more appropriate. If a similar index already exists, adding another one could increase storage and make every insert and delete more expensive.
Test the selection query, inspect logical reads and the execution plan, and confirm that the index helps the complete workload rather than one isolated statement.
Tune for Sustainable Throughput
We tested several approaches before settling on batches of 25,000 rows at roughly six-second intervals. Those values describe one database, table, index layout, and traffic pattern. They are not universal defaults.
Start with a conservative batch size and monitor:
- Azure SQL CPU, data I/O, and log I/O utilization.
- Application latency and errors during the cleanup.
- Blocking, deadlocks, and lock escalation.
- Batch duration and whether it increases over time.
- The number of rows actually removed per batch.
- Transaction-log pressure and available database space.
A short delay between batches may reduce peak pressure and give application work room to proceed. More importantly, the process should be able to reduce its batch size or pause when resource utilization crosses an agreed threshold.
The target is not the highest delete rate observed during a quiet minute. It is the fastest rate the production system can sustain while still meeting its normal obligations.
Inspect the Database After the Purge
Deleting rows does not automatically return data-file space to the operating system, nor does a high fragmentation percentage automatically prove that every index should be rebuilt.
After the cleanup, inspect index page count, logical fragmentation, and page density. Then connect those measurements to the queries that matter. In our review, one nonclustered index showed approximately 59% fragmentation while retaining approximately 99% page density. That deserves investigation, but the percentage alone is not a complete maintenance decision.
Index rebuilds and reorganizations consume resources too. Microsoft’s current guidance emphasizes evaluating fragmentation and page density alongside workload performance instead of relying only on fixed percentage thresholds.
Treat Data Retention as a Product Capability
The best large delete is often the one you never allow to accumulate.
Once the immediate purge is complete, turn the same retention rule into a scheduled, observable process that removes smaller amounts regularly. Record the checkpoint, rows deleted, duration, and database-resource levels. Alert when the job falls behind or batch time changes materially.
For extremely large or predictable retention boundaries, table partitioning and partition switching may be worth evaluating. That is a larger architectural decision and works best when designed into the table rather than introduced during an emergency cleanup.
Our successful approach was deliberately unexciting: choose an efficient access path, delete a bounded amount, commit, observe, and repeat. That discipline turned an operation that threatened the rest of the system into controlled background work.
If you have completed a similar cleanup, what ultimately limited your throughput: the transaction log, blocking, the selection query, or the surrounding application workload?