How to Delete Millions of Rows using T-SQL with Reduced Impact

In this post, I talk about deleting millions of rows in SQL Server whilst keeping impact low.

Deleting millions of rows in one transaction can throttle a SQL Server

TRUNCATE TABLE – We will presume that in this example TRUNCATE TABLE is not available due to permissions, that foreign keys prevent this operation from being executed or that this operation is unsuitable for purpose because we don’t want to remove all rows.

When you run something like the following to remove all rows from your table in a single transaction,

DELETE FROM ExampleTable

SQL Server sets about the process of writing to the transaction log all of the changes to be applied to the physical data. It will also decide on how it lock the data. It’s highly likely that the optimizer will decide that a complete table lock will be the most efficient way to handle the transaction.

There are potentially some big problems here,

  • Your transaction log may grow to accommodate the changes being written to it. If your table is huge, you run the risk of consuming all the space on your transaction log disk.
  • If your application(s) still requires access to the table and a table lock has been placed on it, your application has to wait until the table becomes available. This could be some time resulting in application time outs and frustrated users.
  • Your transaction log disk will be working hard during this period as your transaction log grows. This could be decreasing performance across all databases which might be sharing that disk for their transaction logs.
  • Depending on how much memory you have allocated to your SQL Server buffer pool, there could be significant drops in page life expectancy, reducing performance for other queries.
  • The realisation that a big performance issue is occurring lends temptation to kill the query. The trouble with that is it can delay things even more as the server has to rollback the transaction.ย Depending on how far along the operation is, this could add on even more time to what was originally going to be.

For example, if you kill the query and it is 90% done then the server has to rollback a 90% completed transaction. This will vary but the rollback can take as much time as the delete operation was in progress!ย (check using KILL n WITH STATUSONLY)

Some ways to delete millions of rows using T-SQL loops and TOP

Use a loop combined with TOP and delete rows in smaller transactions. Here are a couple of variations of the same thing. Note that I have arbitrarily chosen 1000 as a figure for demonstration purposes.

FROM LargeTable

And another way…

FROM ExampleTable

GOTO DoItAgain

These are simple examples just to demonstrate. You can add WHERE clauses and JOINS to help with the filtering process to remove specifics. You would add error handling/transactions (COMMIT/ROLLBACK) ย also.


It’s a bad idea to delete millions of rows in one transaction ๐Ÿ™‚ and whilst this might sound like a no-brainer, people do try and do this and wonder why things start to go bad.

Breaking the delete operation down into smaller transactions is better all round. This will help reduce contention for your table, reduce probability of your transaction log becoming too large for its disk and reduce performance impact in general.

Your transaction log disk will still be working hard as your refined delete routine removes the rows from your table. Try and run this task during maintenance windows which are typically done inside off peak periods.

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on


  1. Hi Andy,
    Very Nice article… good work.

  2. TIRUMALESH says:

    Very Good Site…Nice articles…Keep it up…

  3. Patrick says:

    Mate thanks a bunch, that is so much faster I’m very surprised! ๐Ÿ™‚

  4. Exactly what I was looking for. Thanks Andy!

  5. Adnan Khan says:

    Thanks a lot Andy..This is exactly what I was looking for.. You saved me…
    Once again thank you so much for sharing a nice article…

  6. Similar to your top rows solution, the following works as well:

    set rowcount 50000
    do while (1=1)
    delete mytable where
    if @@rowcount=0
    set rowcount 0 — Reset the rowcount

  7. Nice article. Assuming that disk space for the transaction log growth is not a concern, and assuming that other queries being blocked is not a concern, is a single large operation faster than multiple smaller “group type operations”? I’m not a DBA and I’m trying to get my head around the situation from a theoretical standpoint.

    • Thanks very much Johnny. I’m glad you liked the article. You have raised an interesting question. Typically SQL Server is far more efficient at dealing with sets of data so in the scenario you refer to where disk space and locking is not an issue, I would expect a single large transaction to be faster. It’s something worthy of a blog post to try and prove this. ๐Ÿ™‚

  8. Hi Andy
    Great article well explained. But can you explain why is it necessary for the SELECT 1 at the beginning of the code.

  9. Adam Feather says:

    I’ve just forwarded this article around my dept. as I suspect we suffered at the hands of a large delete the other day. Hope you’re well!

  10. Gavin Stevens says:

    I started this route. 94,000,000 rows in a logging table.. The data file is 95 GB in size.. I’m nearing capacity on a 128GB drive. Your method is better than a single delete command, but will still take forever to run. I found it more effective to simply drop and re-create the logging table and then shrink the database, filesize went from 95GB to 56 MB!

Speak Your Mind