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.
Best Answer
Some things you can look at...
Reduce the batch size from 10000 to something smaller, like 2000 or 1000 (you didn't say how large your row size is).
Try turning on IO Stats to see just how much IO the FK lookups are taking.
What is the waiting caused by when the insert it happening (master.dbo.sysprocesses)?
Lets start here and see where we go.