1

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

7
  • 1
    Can it be totally unrelated to the query plan and just related to activity on the table (as in: locks). Particularly the 5s to 40s make me think someone is not too smart in their locking strategy. And you are on the receiving end. Such issues would not show up on a query plan.
    – TomTom
    May 24, 2016 at 12:06
  • Can you tell what are Fill Factor and Fragmentation for clustered and other indexes on the table? Insert into the middle of a Clustered index is slow thing upfront.
    – Stoleg
    May 24, 2016 at 13:38
  • Fragmentation at the moment is about 1-2%. Fill factor is set to 80, currently about 81.
    – James
    May 24, 2016 at 15:25
  • As for locking, that could be a factor. If you look at the stats of the query the reads and cpu time are very low; it's possible that 99% of the 'query time' is actually just waiting for locks to expire.
    – James
    May 24, 2016 at 15:28
  • You need to see what you're waiting on. sp_blitz could be a good place to start (github.com/BrentOzarULTD/Sql-Server-First-Responder-Kit). The other thing I'd be interested in - you've tagged it as Availability Group...how is that setup? If synchronous then it could be waiting for the replication to complete before committing.
    – Greg
    Jan 13, 2018 at 3:51

1 Answer 1

0

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/

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