We have an oltp application and I am trying to troubleshoot why insert into one of our table is taking close to a second. Not all inserts are taking this much time but there are some which does take close to a second and since this insert is part of business transaction which can only take around 200ms, a one second insert is a problem.

This table has a primary key which is identity and is nonclustered. Clustered index is another column.

The insert stored procedure is very simple. Something like

Create proc usptable_insert
( a bunch of parameters)
insert into table (col1, col2 ........)
values (parameters1, parameter2...... etc)
insert into table2 (col1, col2 ........)
values (@Id, parameter2...... etc

We are in the process of migrating from table1 to table2 hence inserting into two tables and using identity insert to make sure table2 has same keys.

First two rows are the the insert statements enter image description here

Initially I thought it was because of latch contention because of hotspot issue on the last page. But I tried to reproduce this in pre-prod by running this stored procedure through sqlstress with 100 threads and couldn't.

If I am not wrong this behaviors cannot be blocking as its an insert and unless something is blocking the whole table which is not the case.

What can be other reasons for this behavious?

  • Tested in pre-prod by doing inserts that would cause pagesplit, but that did not bring up the execution count to a second.
    – ilovesql
    Sep 26, 2018 at 1:57

1 Answer 1


If I am not wrong this behaviors cannot be blocking as its an insert and unless something is blocking the whole table which is not the case.

If there are bigger queries reading data from table or table2, they could take a table-level shared lock, resulting in blocking.

You could test with read commited snapshot isolation level and use row versioning if blocking is the case.

You need to know the implications (version store will grow, long running open transactions can be problematic for your tempdb, row versioning is on query level, not transaction level.)

To enable RSSI:



Look into what your app supports, an example of when not to use RCSI is when application code is counting on pessimistic locking. More on that here


Another reason could be an autogrowth event triggering as a result of the insert.
The time this takes depends on the autogrow settings, hardware, instant file initilization, .... this could take longer than expected, and be a blocking factor for your inserts as they have to wait for these new pages to be available.

Trace them with an extended event. This event should also provide you the query that made the autogrowth occur.

Pregrowing your files on idle moments could work to solve this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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