Bulk Insert Query Performance Issue
When this query runs it is doing almost 4 million page reads according to the query statistics captured while it ran. Total the table consists of 7 million pages. And it is taking between 10-20 seconds to run each batch of Inserts. The query plan you are seeing does not seem to reflect that, but it is happening. I also checked and the statistics look good and there is no fragmentation. For safe measure, I ran an update on stats and rebuilt indexes prior to testing.
There is a unique NC index on this table that consists of several columns so speculate that may be what is happening
I captured the XML in SQL and loaded that into Sentry Plan Explorer so that is what you are seeing.
Here is a sample of the stats from the trace for this query.
My question is why this query is performing work that it does not seem like it should be. Why is it reading some many rows and taking so long to run? And is there something that you suggest to do to improve this?