I have an INSERT statement running at a customer site that is giving me problems.
It will happen in milliseconds several times, then the next time take a couple minutes, then several in milliseconds -- followed by one that takes nearly an hour!
It's an INSERT ... (SELECT ... from TableA ...join ... join ... join)
Last month I added an index on TableA to better cover its WHERE clause & joins, and the problem was solved.
Yesterday I got the complaint again. So I rebuilt the index I had created. No better. I updated stats on the table. No better. I rebuilt all indexes and updated all stats. No better.
The INSERT being within a trigger, it's a little hard to analyze exactly ... so for purposes of reviewing the execution plan I substituted the table into which I'm inserting for the INSERTED table. I think that is valid ... but it is something I've changed.
The execution plan shows 80% of the time being taken to prepare the inserts into 4 indexes on the table into which we're inserting.
I suspected that meant we had a disk writing bottleneck ... but decided to check a few performance indicators ... which found the disk at 100% idle most of the time with a 0-length queue most of the time. That doesn't sound like a bottle-neck.
I tested moving the problem indexes into a separate filegroup on a different physical disk ... but the execution plan made it look like that was no better. And again, my disk performance indicators look OK.
What do you recommend I do to try to regain performance?