Concurrency Week: How to Delete Just Some Rows from a Really Big Table

Say you’ve got a table with millions or billions of rows, and you need to delete some rows. Deleting ALL of them is fast and easy – just do TRUNCATE TABLE – but things get much harder when you need to delete a small percentage of them, say 5%.

It’s especially painful if you need to do regular archiving jobs, like deleting the oldest 30 days of data from a table with 10 years of data in it.

The trick is making a view that contains the top, say, 1,000 rows that you want to delete:

Make sure that there’s an index to support your view:

And then deleting from the view, not the table:

This lets you nibble off deletes in faster, smaller chunks, all while avoiding ugly table locks. Just keep running the DELETE statement until no rows are left that match. It won’t necessarily be faster overall than just taking one lock and calling it a day, but it’ll be much more concurrency-friendly.

Wanna see it in action? No? Then just copy/paste my code, put it straight into production like you always do, and get back to work. For the rest of you, keep reading.

Demoing Fast Ordered Deletes

To demo this technique, I’m going to use the cloud setup for our Mastering Query Tuning classes:

  • An 8-core, 60GB RAM VM with the data & log files on ephemeral (fast) SSD
  • The Stack Overflow public database as of 2017-Aug
  • The dbo.Comments table – which has 60M rows, 20GB in the clustered index
  • I’ve created 5 nonclustered indexes that total about 5GB of space (to make the deletes a little tougher and more like real-world tables)

The Comments table has a CreationDate field, and let’s say I need to delete the oldest comments – we’re going to delete all the ones from 2008 and 2009:

Comments by year

2008 & 2009 had a total of 1,387,218 comments – but that’s only about 2.3% of the table’s overall rows.

First, the plain ol’ DELETE.

I could try just deleting them outright:

It takes 39 seconds. Here’s what the actual execution plan (PasteThePlan) looks like:

DELETE dbo.Comments WHERE CreationDate < ‘2010-01-01’

It’s what we call a “wide” execution plan, something I first heard from Bart Duncan’s post and then later Paul White explained in much more detail. Because we’re deleting so many rows, SQL Server does a bunch of sorting, and those sorts even end up spilling to TempDB.

Plus, it’s taking a big table lock as it works. That’s no good, especially on big tables.

If you can get away with a 39-second table lock and activity in TempDB, the plain ol’ DELETE technique is fine. But let’s pretend you’re working in a mission-critical environment where a 39-second table lock is out of the question, and you need a faster background technique.

Demoing Fast Ordered Deletes

Like we talked about at the start of this odyssey, create a view:

Make sure that there’s an index to support your view:

And then deleting from the view, not the table:

It runs nearly instantly (because we’ve got an index to support it), and here’s the plan:

Fast ordered deletes plan

At first, it looks the same as the plain DELETE plan, but look closer, and there’s something missing:

Just like me with the tequila – no spills

There’s no yellow bangs because there’s fewer sort operators and they’re not spilling to disk. Similarly, the memory grant on this query is way lower:

  • Plain DELETE memory grant: 118MB (only 64MB of which gets used, but it spills to disk anyway because not every operator can leverage the full grant – you can learn more about grant fractions from Joe Obbish)
  • Fast Ordered Delete memory grant: 1.8MB (only 472KB of which got used)

The grants are lower because we’re handling less data, which is also evidenced by the STATISTICS IO output:

  • Plain DELETE logical reads: 25,022,799 on the Comments table (plus another 4.1M on the worktables)
  • Fast Ordered Delete logical reads: 24,732 on the Comments table, plus 2K on the worktables – but that’s with me using TOP 1,000 in the view. If I change it to TOP 10,000, then the reads jump to 209,163. Still way better than 25,022,799 though, but it brings up a good point….

If you need to do this regularly, tune it.

You can play around with:

  • The number of rows in the view (say, 1K, 5K, 10K, etc)
  • The delay time between deletions

That way you can find the sweet spot for your own deletes based on your server’s horsepower, concurrency demands from other queries (some of which might be trying to take table locks themselves), the amount of data you need to delete, etc. Use the techniques Michael J. Swart describes in Take Care When Scripting Batches.

More learning on this topic:

And if you have to do this kind of thing often, check out Kalen Delaney’s concurrency class. It’s on sale this week!

Previous Post
Concurrency Week: An Odd Case of Blocking
Next Post
[Video] Office Hours 2018/4/25 (With Transcriptions)

57 Comments. Leave new

  • Brendan Morgan
    April 27, 2018 8:59 am

    This is a pretty interesting strategy. When do you prefer it over creating partitions and dropping the oldest partition as it expires?

    Reply
  • alen teplitsky
    April 27, 2018 9:25 am

    What about set rowcount? Find some number where you can delete data without blocking apps. Set it up as a job and run it every few minutes.

    Reply
    • Alen – sure, try that with the Stack Overflow databases and compare how the query plans, duration, and logical reads look. That’s one of the things I love about using that database for demos – if there’s a technique you think will work well, you can totally try it out! Give it a shot and see if performance matches what you expect. (Hint: it doesn’t, heh.)

      Reply
    • Noah Engelberth
      April 27, 2018 10:58 am

      The other thing you’ll run into with SET ROWCOUNT is that it’s removed as a method for limiting the number of rows effected by DELETE, INSERT, and UPDATE statements as of SQL Server 2014 and above (https://go.microsoft.com/fwlink/?LinkId=798563) — though, functionally, DELETE TOP (1000) FROM dbo.table ORDER BY sortColumn is the replacement for the older SET ROWCOUNT syntax

      Reply
  • What about the overall added cost/time on the front end for updating the index for all new inserts into the base table? Those are one at a time and add a fractional bit more cost/time overall. That’s probably a lot more survivable/less of a problem than the locking/blocking on the delete side though.

    Reply
  • Thank you for this demo. I’d been seeing those “wide” execution plans on deletes but hadn’t looked at it in detail. Would the big table lock be less of the problem when using read committed snapshot isolation?

    Reply
    • RussellH – you’re welcome! It would be less of a problem for selects, but when folks run into this problem, it’s usually because they’re also trying to do deletes/updates/inserts at the same time. RCSI doesn’t help you as much when 2 people are trying to write to the same row(s).

      Reply
  • Joshua Grippo
    April 27, 2018 11:21 am

    When you say keep deleting records, until there are no more left, do you means something like:

    select ‘prime the row count’
    while @@ROWCOUNT0
    DELETE dbo.Comments_ToBeDeleted WHERE CreationDate < '2010-01-01'

    Reply
  • Rebecca Lewis
    April 27, 2018 11:33 am

    Very clever, Brent. Thanks!

    Reply
    • Rebecca – thanks! I can’t claim credit for it by any means – Microsoft’s posting of it was the first time I’d seen it, but just wanted to get a fresh, solid version out there since their version disappeared in the continuous SQLCAT blog post reorgs.

      Reply
  • Robin Willoughby
    April 27, 2018 12:22 pm

    I know this may not work for everybody – but we copy the rows we want to keep into a temporary or work table, truncate the original table and then copy the rows back

    Reply
    • Robin – right, the key is at the start of this post when I say, “things get much harder when you need to delete a small percentage of them, say 5%.” You wouldn’t really want to copy 95% of a table out, then copy it back in – your transaction log would explode, major blocking, etc.

      Your technique totally makes sense in the opposite scenario – when you only want to KEEP 5% of the rows, and you wanna delete the other 95%. I love it then.

      Reply
  • Is this any more efficient than just doing DELETE TOP (1000) FROM dbo.Comments WHERE CreationDate < '2010-01-01' until it doesn't delete anything? I see how it's better than deleting everything in one batch, but is the view doing anything magical that a TOP clause in the delete wouldn't do?

    Reply
    • Wyatt – go ahead and try it with the Stack Overflow database and take a look. The view indeed does something magical. That’s why I work with publicly available databases so y’all can do this stuff yourself instead of asking me to do it. (I’m not mad – just want y’all to get up and do some investigation yourself so you can see this stuff and learn.)

      Reply
  • You can’t create a view with ORDER BY, it violates the relational model. Also, it is a nondeterministic, because there is no tier breaker in the ORDER BY.
    This should be specified in the blog, I guess

    Reply
    • Calin – if you read the post, I *did* create a view with order by. 😉 You’re absolutely right about it not being deterministic, but for the purposes of this blog post (remember – read the title first, then work down) it works great.

      I really do wish I could cover every topic in every blog post, but there’s only so much I can cover per post.

      Reply
  • You can also use a CTE.

    WITH Comments_ToBeDeleted AS (
    SELECT TOP 1000 *
    FROM dbo.Comments
    ORDER BY CreationDate
    )
    DELETE FROM Comments_ToBeDeleted
    WHERE CreationDate < '2010-01-01';

    I don't have the stack overflow database, but in my tests if you move the where clause inside the cte, it changes the non clustered index scan to a seek. But that didn't seem to affect the performance.

    Reply
    • Nicholas – I just tested that in Stack, and it’s a nonclustered index scan (not seek), but it’s a good scan in the sense that it doesn’t read the entire table – only enough rows to achieve the goal. Estimated number of rows to be read is in the tens of millions, but the actual number of rows is only 1000 – so in this case, the scan is fine. I’d be totally fine with the CTE. Nice work!

      Reply
    • Alex Friedman
      April 30, 2018 8:58 am

      Yup we use a CTE too, works like a charm.

      Reply
  • Ricky Lively
    April 28, 2018 7:40 am

    Do not forget you can do groups with Kenneth Fisher’s GO “batches”

    DELETE dbo.Comments_ToBeDeleted
    WHERE CreationDate < '2011-01-01';
    GO 401
    (https://sqlstudies.com/2018/04/12/deleting-a-lot-of-data/)

    Reply
    • Ricky – yes, but with a few big exceptions. That only works in SSMS, it will repeatedly retry the query even when there’s no rows left to be deleted – not a great fit for something you want to automate – and there’s no error handling. (I like Kenneth’s post, though.)

      Reply
      • To be fair I was dealing with a very specific set of circumstances. Limited log space, no index, one time delete and an environment that wasn’t in use yet. I also had an exact count of the number of rows I’d be deleting so could calculate how many iterations I needed ahead of time 🙂

        Reply
  • Robert Mackenzie
    April 28, 2018 8:10 am

    We do something similar but without the view (and constant clicking). This gets it done in one swoop without taking huge locks. You can stop the query any time you need to and continue it until it’s done. I’ll test it on the SO but would imagine it uses the exact same query plan.

    declare @rowCount int = -1;
    while(@rowCount 0) begin
    delete top 1000 dbo.Comments
    where CreationDate < '2010-01-01';
    set @rowCount = @@rowCount;
    end

    Reply
    • Robert – yeah, the problem I’ve run into with that is that someone tweaks that TOP number, goes past 5000 thinking they’re going to get ‘er done faster, and whammo, you get table locks. If you put it in a view, you make it less likely that someone’s going to change the object (assuming it’s locked down for permissions) and it forces them to keep their locks small. I like the idea though!

      Reply
  • Appreciate it and yes, maybe it’s time to stop reading blog posts diagonally at 2 AM 🙂

    Reply
  • Henrik Staun Poulsen
    April 30, 2018 1:25 am

    Brent,
    If I have the disk space for an extra copy of the table, then I find that it is sometimes better for concurrency to copy all the rows you want to keep, and then do two sp_rename operations. The caveat is that it requires two Schema Modification locks, (which are quick once they are allowed to run).

    Reply
    • Henrik – yes, but not usually when we’re talking only deleting 5% of rows from a really big table. If you’re copying 95% of a really big table to the transaction log, that presents its own challenges.

      Reply
      • Henrik Staun Poulsen
        April 30, 2018 6:00 am

        hi Brent, yes, transaction log will be big, _if_ you do not run it as smallish batches, and the database is in SIMPLE mode.

        Reply
  • Henrik Staun Poulsen
    April 30, 2018 1:37 am

    Brent,
    I hate the IX_CreationDate index.

    Bit here is a case where “Know your data” applies.
    Often there is a correlation between CommentsID and CreationDate.
    This can be used if you just want to trim down your table.
    Something like this:
    DECLARE @lower BIGINT
    SELECT TOP (1) @lower = CommentsId FROM dbo.Comments ORDER BY CommentsId
    DELETE TOP (1000) FROM dbo.Comments WITH (ROWLOCK)
    WHERE CommentsId>=@lower AND CommentsId < @lower+1000
    AND CreationDate< DATEADD(YEAR, -3, GETUTCDATE())

    Then you do not need that extra index on CreationDate, which I find is an important save of I/Os.

    Reply
    • Henrik – yep, that’s true too! We cover that in our Mastering Query Tuning classes. (Just only so deep I can go in one blog post – holy cow, y’all want me to write a book on this, apparently, hahaha.)

      Reply
  • I’m being thick, why does the view help over just a good index thanks Geoff

    Reply
  • […] Brent Ozar has a tip for deleting a relatively small percentage of data from a very large table: […]

    Reply
  • “Wanna see it in action? No? Then just copy/paste my code, put it straight into production like you always do, and get back to work. For the rest of you, keep reading.”

    To be fair, I always cite you as the source when it blows up in production. 😉

    Reply
  • The best bit is in the comments: “if there’s a technique you think will work well, you can totally try it out”.

    I’ve got a “consulting line” that I’m beginning to use more and more. “I don’t know. I can’t tell by inspection whether X is true, measure it and tell me how it goes”.

    I mean, I can sometimes guess how SQL Server will behave, But the best one to tell how SQL Server will behave is SQL Server.

    (And thanks for the plug again!)

    Reply
    • Hahaha, thanks sir. Yeah, it gets a little frustrating when I get dozens of comments that say, “You should try ___ and write about how it works.” What the hell, people, go start your own blog and pay things forward, hahaha.

      But at the same time, I gotta be thankful that I have such a vocal audience. That’s awesome – at least I’m not in silence heh.

      Reply
  • Sid Mudassir
    May 2, 2018 11:19 am

    Nice Post Brent, Like Always !
    One concern though, if we need to delete from multiple tables , which does not happen to be related whatsoever, then we need to create multiple views and indexes for them (if doesn’t exist one). Then, probably create SP to delete records off of those tables at once?

    Reply
  • […] Concurrency Week: How to Delete Just Some Rows from a Really Big Table Deleting a batch of rows might be painful. Check this Brent Ozar’s post how to relieve this pain. […]

    Reply
  • Nice post Bret,

    I tried the solution but found some issues:
    – view must be schema bound
    – index views do not allow use of TOP keyword
    – cannot create a nonclustered index for a view if there is no unique clustered index

    Am I missing something that could be the cause of all the issues above?

    Reply
  • (Let ‘s see if this posts the code properly) Here’s the version we use. The idea was someone else’s (I wish I knew who, so I could cite), but works overall quite well.

    It uses the CTE to do the delete as mentioned by Nicholas above, but with the added advantage that it’s constantly narrowing the window it queries.

    IF OBJECT_ID(‘tempdb..#mydeleted’) IS NOT NULL
    DROP TABLE #mydeleted
    CREATE TABLE #mydeleted
    (insert_datetime DATETIME)
    CREATE CLUSTERED INDEX cidx_mydeleted_insert_datetime ON #mydeleted (insert_datetime)

    DECLARE @msg NVARCHAR(50),
    @insert_datetime DATETIME

    SELECT ‘Starting’
    –sets @@ROWCOUNT
    WHILE (
    @@ROWCOUNT 0
    AND GETDATE() = @insert_datetime
    ORDER BY insert_datetime
    )
    DELETE FROM cte
    OUTPUT deleted.Insert_Datetime
    INTO #mydeleted
    END

    Reply
  • (Nope, that didn’t post properly; Brent, feel free to delete that one)
    Here’s the version, based on a couple concepts (CTE, narrowing the window) that others I can’t find, pointed out.
    IF OBJECT_ID(‘tempdb..#mydeleted’) IS NOT NULL
    DROP TABLE #mydeleted
    CREATE TABLE #mydeleted
    (insert_datetime DATETIME)
    CREATE CLUSTERED INDEX cidx_mydeleted_insert_datetime ON #mydeleted (insert_datetime)

    DECLARE @msg NVARCHAR(50),
    @insert_datetime DATETIME

    SELECT ‘Starting’
    WHILE (
    @@ROWCOUNT 0
    AND GETDATE() = @insert_datetime
    ORDER BY insert_datetime
    )
    DELETE FROM cte
    OUTPUT deleted.Insert_Datetime
    INTO #mydeleted
    END

    Reply
  • (gods, neither time did the code post correctly – Brent, please save us from my crappy posts)
    Here’s a way I came up with, based off other smarter peoples’ ideas. I can’t post the code here, so instead you get a link. http://thebakingdba.blogspot.com/2015/01/t-sql-more-efficient-delete-using-top.html

    Basically it uses the CTE like Nicholas’ example, but it also takes the results (via #DELETED) and uses that on the next loop to narrow the window, since you know you’ve deleted up to a certain point. So it won’t slow down over time as it runs, since it would find fewer and fewer matches.

    Hope this helps!

    Reply
  • Hi! what about Transaction Log with this technique?, if i need delete a several millon records?, how do you apply the commits using the view(top 1000 or n), i would need to use a cursor?

    Reply
  • Hi, need your help guys
    I need to keep only top 50 rows from each group after sorting in an unsorted table and delete the rest. Can someone help me on this.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}