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