MadAdmin, yes literally Bulk Insert statements straight from a file with no further joins or filtering or sorting.

It has a bigint identity which is populated automatically, plus one index on another bigint which is from the file. That's one number per file. So hopefully it's not sorting in any special way after that. I will be experimenting with some of those sorting options and adding indexes/before after (I do plan to experiment to work out how to speed this stuff up).

8 files for tempdb, 16 cores and separate disks. I think the underlying storage is SAN and the network cards are being saturated so we're upgrading them (virtually, it's Hyper-V stuff).

Log waits are definitely one of our bottlenecks but I expected that the sessions waiting on the log would go to a suspended state if they're not being serviced; not form blocking chains where a b and c are blocked by d.

I'm currently getting 20-30-40 Bulk Inserts all waiting in massive queues on each other. I wonder if there's an internal limit, or if possibly they're saturating the schedulers, and maybe using a MAXDOP hint would help? I'll add that to the list of things to try.

The database is in simple mode; but it's also multi-terabyte. We'd actually like to make it highly available; but I can imagine that AlwaysOn and full mode are going to start killing things. Also I'm not sure if TF 610 operates on and can improve bulk inserts on simple mode databases; I suspect not. I haven't seen much if anything on performing mass bulk inserts in AOAG environments. I'm even going so far as to investigate In-Memory OLTP stuff to see if we can just eliminate this step (because it is basically staging stuff which will be put to disk elsewhere later anyway).

John -> Yes, I'm using SQL Sentry and also sp_WhoIsActive of course! However they only identified what I've listed so far; no actual root causes. This is a pretty heavy system and I expect there to be lots of bottlenecks. What I'm particularly interested in though is how the blocking chains are forming. Multiple sessions waiting on log writes usually would not form blocking chains. One normally has to hold locks on resource that others require and log writes aren't a lockable resource. But I haven't found any indication of what it is.