Any thoughts as to improve upon a Table Insert bottleneck?

Mr Sensible 2014-10-08 15:47:51

The plan has a bottleneck on a table insert and this creates occasional timeouts for the app. Any thoughts? I can not get the actual execution plan it runs out of hours 🙁

sqldriver 2014-10-08 16:22:34
Are you actually trying to insert 1.4 million rows at once?

You should be batching transactions of that size.

Aaron Bertrand 2014-10-08 19:12:39
You have a parallel distinct sort before an insert of 1.4 million rows. An insert of 1.4 million rows is going to cost what it's going to cost – it's not just the insert but also side effects like page splits, trigger activity, logging… I agree with sqldriver that you should consider batching these into smaller chunks (I wrote an article about a similar concept around deletes), but you could also consider changing the query (we can't see the text, because you've anonymized it) to (a) not use DISTINCT – because I doubt that's necessary and (b) not order by 30 or so columns (this may be caused by (a)).