I have a table with records logs. Every few minutes I delete old logs by Service. Each deletion can have almost 10,000 records. The question is whether to delete all the logs with delete top, a few logs in loop or deleting them all at once will be more effective? I have indexes and there is no cascade on the tables.
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.
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.