I know that an INSERT on a SQL table can be slow for any number of reasons:
- Existence of INSERT TRIGGERs on the table
- Lots of enforced constraints that have to be checked (usually foreign keys)
- Page splits in the clustered index when a row is inserted in the middle of the table
- Updating all the related non-clustered indexes
- Blocking from other activity on the table
- Poor IO write response time
- ... anything I missed?
How can I tell which is responsible in my specific case? How can I measure the impact of page splits vs non-clustered index updates vs everything else?
I have a stored proc that inserts about 10,000 rows at a time (from a temp table), which takes about 90 seconds per 10k rows. That's unacceptably slow, as it causes other spids to time out.
I've looked at the execution plan, and I see the INSERT CLUSTERED INDEX task and all the INDEX SEEKS from the FK lookups, but it still doesn't tell me for sure why it takes so long. No triggers, but the table does have a handful of FKeys (that appear to be properly indexed).
This is a SQL 2000 database.