Insert into table with identity insert taking almost a second

Question

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)
as
insert into table (col1, col2 ........)
values (parameters1, parameter2...... etc)
DECLARE @Id INT = (SELECT SCOPE_IDENTITY());
SET IDENTITY_INSERT [table2] ON
insert into table2 (col1, col2 ........)
values (@Id, parameter2...... etc
SET IDENTITY_INSERT [table2] OFF

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

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?

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:

ALTER DATABASE MyDatabase  
SET ALLOW_SNAPSHOT_ISOLATION ON  

ALTER DATABASE MyDatabase  
SET READ_COMMITTED_SNAPSHOT ON  

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

Autogrowth

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.