Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

How can I stop slow insert on a table?

Asked by Buffy Heaton Feb 7, 2023 418 views 1 answer
Share

About this question

 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 others  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.

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.