The question came up as a blog comment of “To delete 100,000 row chunks from a 9,542,067 row table, how about”
Thats a great question. I have had to do something very similar lately.
The problem that you run into when you are deleting 100,000 rows is lock escalation.
When you run a delete statement with just a few rows or a small number of rows server generally locks the pages associated with those rows. When you cross a threshold somewhere between 2000 rows and 4000 rows depending on your server the page locks get escalated to a table lock, thus locking the entire table until all 100,000 rows are deleted which could take some time.
When I want to delete many rows from very large tables, I will usually do it in batches of 2000 rows at a time or less per transaction.
Unfortunately the CTE doesn’t help or hurt in any way here.
My recommendation would be to create a loop that checks to see if there are rows that need to be deleted based on your join, then deletes them at 1000 to 2000 at a time.
Another way to do it would be to get a count of the rows to be deleted and then loop adding up the number of rows deleted (1000 to 2000 at a time), and delete until they are all gone.
Also you might want to consider index structure, fragmentation and statistics when deleting that many rows. When deleting large numbers of rows I have sometime put in an index regorg somewhere along the way or a statistics update, just to help speed up the process.
Another thing to consider if you don’t have a good index for your where clause or join criteria would be to add a filtered index for the data you are deleting, then when you are done deleting you can drop the filtered index.
There really isn’t any great way to delete lots and lots of rows, unless of course you want to delete everything from the table with truncate table.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
I lately used a view to delete a given amount of rows in a loop. doing some researchs and tests it seems, that it is a bit faster than deleting the rows directly from the table. has anyone else experienced better results using views for deletes on big tables?
Thanks Gerald, I will give that a try.
Have you seen the fast ordered deletes trick? It sounds crazy, but it’s really cool: https://www.johnsansom.com/fast-sql-server-delete/
I keep saying I’m going to blog it and show execution plans, and I’m at work early today, so today should probably be that day.
Thanks Brent. That link really describes it well. I will give it a try and see how it goes. If you write a blog showing the execution plans, please post the link back here.
delete top (1000) from table_name where = ?
go 10
It is very nice to read your site, thank you very much for your work, it’s great!