P.S. Make sure every foreign key pointing to a table subject to purging has a corresponding index on the foreign key columns. If no such index is present, a table scan...

Ah, purging... I see several possible approaches. One is to forget purging, and periodically switch all your writes to a new database. Assuming that is not an option, there are...

Although I do not have time to analyze your code in depth, I have used the following technique for purging:

While exists (...stuff you want to purge...)

begin

set rowcount 100     -- experiment with...