In case you didn’t recognize it, it’s actually code that Microsoft wrote. It’s from
sp_delete_backuphistory, and it is plum awful.
I have a lot of personal dislike for this one, because after inheriting a server with a 25GB msdb, I nearly crashed it running this proc on one day of backups.
Back then, a really smart guy told me something: If you make a copy of it that uses temp tables instead, it’s a lot faster.
The issues I have with it can be distilled into three things
- Table variables, c’mon
- Running eight different deletes inside one BEGIN TRAN
- TABLE VARIABLES, C’MON
Anyone who has processed data in volume knows how bad table variables can be. The inserts are forced serial, they spill to tempdb a lot, and unless you recompile (which this proc doesnt), you end up with a one row estimate.
Which makes it doubly awkward to trip yourself up inserting to a table variable with a predicate on another table variable. Have mercy.
I have no idea if sticking a PK on any of these might help. Heck, the DML sort might even slow things down.
But it’s not like the optimizer is gonna know you went through the trouble of selecting distinct IDs, otherwise. Table variables get no column level statistics.
This is the thing I like about the temp tables — you can stick an index on them afterwards. In this scenario, I’m in favor of that causing a statement level recompile.
Begin Without End, Amen
The worst thing, though, really is the use of a transaction around eight deletes.
You can have a god awful amount of data in these tables, even in a single day. The server that I had issues with had around 5000 databases on it, getting ~15 minute log backups.
Plus daily fulls and 6 hour Diffs.
Do that math.
The whole time a delete is running, the log backup jobs running couldn’t write to the tables.
If one of them throws an error towards the end, they all have to roll back. Imagine 7 or 8 deletes all rolling back.
Blocking your log backup jobs from writing to tables.
So you can’t take more log backups.
And these are the people who write the product
If you’re an ISV, or someone getting started with SQL Server, you might poke around the code in the system for tips and tricks.
And you’d see stuff like this.
Which may explain some things…
Microsoft — if you’d like some help with SQL Server, click the consulting link at the top of the page!
Thanks for reading!