Very slow insert performance on large table (EF) One of our systems has a table that receives around 20k inserts per day - it currently contains ~10m rows. We've just pushed out a system upgrade and I'm intermittently getting some shockingly slow performance on inserts to this table now (anything from 5 to 40s traces). Inserts will only ever insert a single row at a time. Entity Framework is generating the following SQL:

declare @generated_keys table([ID] uniqueidentifier)

insert [dbo].[Table]([Col1], Col2], etc)

output inserted.[ID] into @generated_keys values (@0, @1, etc)

select t.[ID] from @generated_keys as g join [dbo].[Table] as t on g.[ID] = t.[ID] where @@ROWCOUNT &gt; 0


Nothing out of the ordinary - at least as far as I can see.

The table has 12 FKs. As the entity represents a time-based event, the clustered index is on a DateCreated column. There are 8 further non-clustered indexes, with various included columns. The ID column is the PK and has a unique, non-clustered index.

As a note, I have noticed that, due to an omission, the ID column is using newid() instead of newsequentialid().

The actual execution plan comprises the following major parts:

7x 3% - Clustered Index Seek on FK tables

1x 8% - Table Insert (@generated_keys)

1x 74% - Clustered Index Insert, 1 actual row, 0.09 cost

I'd really like to get to the bottom of this but I'm stumped now. Any advice?
It appears that the dates you are clustered on are not being inserted sequentially.  If your date field was recent purchases for example they would be inserted at the 'end of the table'.  Because it is taking so long to do the insert it seems that SQL is having to reorder your data with each insert instead of tacking it on the end.

Determine if your clustered index is important to reads.  It is always a balance on input / output when looking at indexes.  It may be that a different column is best for clustered index.  I almost always use an integer or long primary key with no business value for the clustered index and then index dates, maybe with included columns, for read performance.

https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/