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.

  • Do you have autoexpand enabled on your data files? That can cause performance problems with the default configuration. – Larry Coleman Jan 10 '11 at 21:14
  • Are we talking about using a profiler? msdn.microsoft.com/en-us/library/ms187929.aspx – Incognito Jan 10 '11 at 21:16
  • @Larry: Data files have significant free space, so I don't believe data file growth is an issue. Good one to add to the "things to check" list, though. – BradC Jan 10 '11 at 21:31
  • @user210: Profiling the statement completion just shows me that it took 90 seconds, it doesn't tell me WHY. Unless there are other events that you think would be more telling. – BradC Jan 10 '11 at 21:32

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.

| improve this answer | |
  • 2
    Lowering the batch size does help (1000 records takes ~25 seconds). That's likely to be our current "workaround". I'll see if I can determine IO Stats and waits (the job is run on-demand by the client when they have a file to process, so I can't always predict when the job will actually run). – BradC Jan 10 '11 at 21:36


You should examine the wait stats for your query. With SQL2000 you could use the DBCC SQLPERF("waitstats") syntax to get those details.

| improve this answer | |

I can say what I am looking for when analyzing the performance of a query. Maybe it helps.

  • analyze query execution plan and check for index scans, table scans, usage of convert_implicit functions for sql data types, parallelism.
  • run the query with SET STATISTICS IO ON and SET STATISTICS TIME ON to see the execution time and read/write io for each insert.
  • check out waittime from sysprocesses for your session spid.
  • run profiler and select standard template. select following: Performance statistics (if repeated then your plan is compiled many times - not good), RPC:completed, SQL:batchcompleted and SQL:batchstarting. Add to them column rowcounts to see exactly the number of rows in the batch. Filter the results to see only your query.
  • at last collect Page Life Expectancy counter from windows perfmon and if it is below 300 (5 min) then the SQL has low memory. Also collect disk counters: disk queue length, Disk Time (your data files drive), Disk Time (your Log files drive) to see if there is pressure on disks.
| improve this answer | |

Try using:





Can be useful in telling you which tables it is doing the most amount of table scans, logical reads & physical reads (I use these three to focus on which part of the query plan needs the most tuning)


Will primarily return the query plan in a tabular format, you can then look at the IO and CPU columns for what is costing the most amount in the query (is it the table scan on your temp table vs the sort it does to insert into your clustered key, etc...)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.