Purge Problems [TSQL Tuesday #027 – The Big Data Valentine’s Edition]

T-SQL Tuesday #27 is hosted by Steve Jones (blog|twitter). This month’s topic is “Big Data”.

Purge Problems in Big Data – Not only a SELECT needs an index

Recently I was called to an issue at a 5+ TB customer. The purge job had stopped working a while back and the disk drives started to feel all bloated.

The purge job ran every night to delete data older than n days. It would execute for over 13 hours and than quit reporting that there were not enough resources to complete the query.

The procedure would go through 5 tables and delete rows based on their relationship to "expired" records in a common parent table. After that, those parent records where supposed to get deleted. But the procedure never got to finish the third delete statement.

The problem was, that each delete would anew request the list of expired records form the parent table in a DELETE WHERE parent_id IN (SELECT id FROM parent); format. The inner select had a where clause checking a date column and an indicator column. While there was an index on the date column, SQL Server could not use it because of the age calculation performed. Also, the indicator was not part of the index. With this setup SQL Server had to perform a table scan of one of the bigger tables in that database for each of the child tables and than one more time for the parent table itself.

There are a few issues with this algorithm, including that by the time the parent table got to be purged more rows might qualify than at the time the first child was purged. That would cause foreign key violations during the delete – but the process never got that far anyway.

To resolve the issue I added the indicator to the index on the date column as an included column. I than rewrote the procedure to retrieved the ids of all to-be-purged records into a temp table, rewriting the select in a way that the index actually could be used. All the delete statements would than join to that temp table to delete the necessary rows. I also made sure that there was an index on the parent_id on each child table – a recommended best practice for all foreign key relationships anyway.

With those changes in place, the purge is now happily humming along again. What we can learn from it is the fact, that while indexes usually help SELECTs and hinder INSERTs and DELETEs, sometimes you need an index to be able to even execute your DELETE statements.

Categories: General, Performance, T-SQL Tuesday