0

I have an insert script running for about 10 + hours now, it's only inserting 8 million rows, but there are few index views built on that table. due the active insert transaction I cannot drop any indexes to speed it up. Is there any solution to speed up an already running insert script?

The table is a wide table with about 125 columns. right now it showing the wait type as BPSORT

1 Answer 1

0

The issue here is that you are doing this in one transaction. You say "only inserting 8 million rows" - but that's all happening in one transaction. And the transaction log is logging all of the insert activity, plus the "few" indexed views. It's not just 8,000,000 rows - which is actually a lot to insert in one transaction into an existing table - but it's 8,000,000 rows of a 125 column wide table - that is a LOT of data being thrown into the table and the log and the indexed views all in one fell swoop.

Your bets are really off for the existing insert. This also sounds like a columnstore table or a table with a columnstore index. At this point you have to either let it finish or cancel it and take your chances with the rollback.

You can potentially query the table WITH (NOLOCK) and see if you can take a guess as to what row is being inserted - based on indexes on the table you may be able to sort by something meaningful out of those 125 columns.

But this needs to at the very least be broken upinto batches where you insert, say, 10,000 or 100,000 or 1,000 rows at a time - but more likely than not you might want to look at a few things here architecturally:

  • Do you really need all of those indexed views?
  • Do you really need 125 columns in one table or should be there be multiple tables here?
  • Can you have a window to batch this in and possibly drop and rebuild indexes after?

Your choices are rather limited right now.

3
  • yeah, this was a one of situation - where no one expected to get 8 million row updates. right now the wait type on this transaction is BPSORT , not sure what that means. Do you think if kill it now would roll back be another 10+ hours? What I can see is the base table inserts are completed, I think IXVs are what is being built
    – Marshal
    Commented May 10 at 19:35
  • Rollback now could be quite excessive, yes. There are a lot of variables here. BPSORT is typically related to Columnstore indexes - does this table have one?
    – Mike Walsh
    Commented May 10 at 19:52
  • @Marshal Rollbacks are single threaded and typically take the same time or longer to undo all of the work that was just done, unless you have Accelerated Data Recovery already enabled, which is only available in SQL Server 2019 and later.
    – J.D.
    Commented May 10 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.