John Deardurff has some advice:
I’ve been reviewing Azure SQL Database performance guidance recently and came across Microsoft’s documentation on How to Use Batching to Improve Application Performance. While the article focuses on Azure SQL Database, the same principles of batching transactions for better performance apply equally well to SQL Server and Azure SQL Managed Instance.
The reason this topic caught my attention is that batching doesn’t just improve performance. It can reduce blocking, minimize rollback pain, improve transaction log efficiency, and potentially lower costs in cloud environments. That’s a pretty good return on investment for a relatively simple coding change. Here is the SQL Script that I use for this demonstration. Feel free to test for yourself. (It is a text file, so you will have to save it as a .sql file.)
Batching is especially important on delete operations against larger tables, where you don’t remove enough data to make TRUNCATE TABLE a viable alternative (or where you don’t have permissions to truncate). But one thing to keep in mind is that index design matters for batch operations. If you don’t have a good index, your first batches will be fast but they will gradually slow down as SQL Server needs to scan an increasingly large range to find the next set of rows to update. I wrote about this quite a while ago when putting together a talk on near-zero downtime T-SQL operations.