This isn't fragmentation.
Fragmentation is generated of course, but deletes will simply create "islands" of remaining pages, which is less evil then GUID/clustered key INSERT fragmentation.
If you're PK is an IDENTITY, then CreationDate should roughly track this so you're actually deleting chunks of contiguous rows anyway.
- Do you have an index on
CreationDate
- Do you have delete cascades?
- Is the TOP 1000 in a single transaction?
For point 3, doing a loop inside a transaction is pointless: is this it?
At some point, a statistics update may be needed if you delete enough rows but I don't think it's that.
Other options:
- why not use TRUNCATE TABLE, wrapped in a stored procedure with EXECUTE AS OWNER
- use SYNONYMs for poor man's partitioning
Depending on how interconnected this table is with others in your database, and assuming that the table only has data for 2013-present, the fastest solution might be:
- Script out your original table, and create a new empty table with the same structure.
INSERT the data for 2016 and 2017 into the new table.DROP the original table.- Rename the new table to the original table's name (see the docs for
ALTER TABLE).
Deletes are generally enough slower than inserts that it's probably faster to copy out 25-30% of the records in the table than to delete 70-75% of them. However, of course, you need to have sufficient disk space to hold the duplicates of the data to be kept to be able to use this solution (as noted by Toby in the comments).
If you do this, you'll want to be absolutely certain that the new table ends up exactly like the original, including any indexes, triggers, etc. You might want to truncate the original, rename it, and keep it around for a while instead of deleting it, just to be sure there's nothing you've missed. Also, outside of any clustered index, you may want to add clustered indexes and triggers after you've inserted the 2016 and 2017 data. If triggers are involved at all, make sure that whatever you do leaves the rest of your data in a valid state.
If other tables reference your table in foreign key relationships (as suggested by Joe Obbish in the comments), then this becomes somewhat more complicated. I would recommend scripting out all the foreign keys that point to this table, removing them, and then recreating them after the new table has been renamed. See this link to an article by Aaron Bertrand for help with this.
Best Answer
Generally:
10k records is tiny. I use loops in a script to bulk delete becasue I want to keep transactions smaller, but I do deltes of 64 million rows each loop.
Generally:
Loops of TOP X, outside a transaction, are more effective than deleting all rows if you do not care about the order of deletes. Reason simply is that every delete is then a separate transaction. Particularly in staging environments when you can for whatever reason not use truncate, this can be good to keep the tx log smaller. It also avoids longer locking because, again, smaller transactions.
But 10k rows is tiny to start with.