1

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

  • 2
    Since your clustered index is not the Identity column AND since you indicated that not every insert takes a long time, you may be experiencing delays due to page splits on the clustered index. There are numerous ways to diagnose page splits - Try searching the Internet. – Scott Hodgin Sep 25 '18 at 12:22
  • That makes sense. I havent pursuit that angle, but since it did not come up in pre-prod, i am less hopeful. Thanks for the comment. – ilovesql Sep 25 '18 at 20:56
  • 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 '18 at 1:57
2

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.

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.